Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Julian Date conversion (Excel 2000)

    We are converting some data records from an IBM AS/400 that uses Julian dates. We want to reformat to Excel on the PC. The only MS document I could find is how to convert an Excel date or serial date to JulianFORMAT. We, of course need to go the other way. We have about 7000 records, so would like a macro to do it in a reasonable amount of time. Anyone done that lately? I haven't in 15 years or so!
    Thanks,
    Gloria

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date conversion (Excel 2000)

    If the julian dates are formatted as text (so that leading zeros display), and if the format is yyddd, and if the dates are in column A, then the following formula could be put in row 1 of any empty column and filled down to convert to Excel date values:

    <pre>=DATE(LEFT(A1,2),1,RIGHT(A1,3))
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date conversion (Excel 2000)

    Thank you, Legare. I assume then, that if it is formatted as yyyyddd it would change to Date(left(A1,4) ... ? I am waiting for actual data now before I be sure. I appreciate the quick response. As a church we have a limited technical staff so these type of forums are invaluable to us!
    Thanks again!
    Gloria
    PS - I was interested to see you are a woodworker. I am a carver and currently starting an large intarsia project just for fun.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date conversion (Excel 2000)

    Yes, your change should work for that format.

    BTW, after inserting the formula, you might want to convert the formula to constant date values so you can delete the original Julian dates and the formula column. To do that, select the column with the formulas and copy them. The select the top cell in the column where you want the dates (could be the coulmn with the Julian dates or the column with the formula), select Paste Special from the Edit menu, click on Values in the dialog box, then click OK.

    I am in the process of building a woodworking shop, and when that is done I hope to learn woodworking. I'm not anywhere close yet. Then just framed the roof of the shop this week.
    Legare Coleman

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: Julian Date conversion (Excel 2000)

    How come you have time for all that!

    zeddy

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date conversion (Excel 2000)

    Because I am a retired old f@rt.
    Legare Coleman

  7. #7
    Lounger
    Join Date
    Nov 2001
    Location
    Woodland Hills, California, USA
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Julian Date conversion (Excel 2000)

    Legare, Got the real data today, and it is yyddd, just as you thought. The formula works just fine and I appreciate the reminder on copying to VALUES, since we'll be doing a 7000 record merge and I always like to use values for that. I really appreciate your help!
    Gloria

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •