Julian Date conversion (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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:

=DATE(LEFT(A1,2),1,RIGHT(A1,3))

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

How come you have time for all that!

zeddy

Reply | Quote

Because I am a retired old f@rt.

Reply | Quote

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

Reply | Quote