converting a (julian) number into a date format

Topic

New Reply

While on the cell in question, right-click. Choose Format Cells. Go to the Number tab. Select the Date formatting. Then choose from the list at the right for how you want it displayed.

Reply | Quote

Replies

I know how to do Format Cells, but if you look at the way I explained the data, that will not work, especially when the year is reversed when the data is brought into Excel. If I click on that cell right now in the spreadsheet and try format dates, it thinks it’s July 11th, 1927 when it should be 2/23/01. I’ve attached the file to this posting.

Reply | Quote

A user in a recent class proposed the following question to me. They have data that they are importing from another database system and it brings in the date in the following format, 10054, which she said is like a Julian format (which I’m not familiar with). 01 is the month. It’s just reversed when it’s brought in from their system. 054 is the 54th day of the year. So she said that 10 054 is really 2/23/01, the 54th day of the year.

Is there an easy way to convert that number into the correct date 2/23/01?

Reply | Quote

Have a look at the Microsoft’s explanation of working with Julian dates

Reply | Quote

Geoff,

I appreciate the link to the article. I tried doing a search there before, but for some reason it didn’t find Julian. Must have been the way I was searching….

Will that solution really work in this case? The user said that the year number has been transposed in their system. (guess theirs is not really a tru Julian format?) When they see 10 for the year, it should really be 01. Is there a easy way to transpose numbers in Excel?

-Kelley

Reply | Quote

Kelley,

That link doesn’t explain a transpoised year; it also only tells how to convert into Julian, not out of julian, as you require.

This one might do it (assuming A1 has the date in Julian):
=DATE(100+MID(A1,2,1)*10+MID(A1,1,1),1,1)+MID(A1,3,3)-1

Reply | Quote

This seemed to work! Thanks so much.

-Kelley

Reply | Quote

If that is in A1, then the following formula will convert it to a date serial value:

=DATE(LEFT(A1,1)+MID(A1,2,1)*10+2000,1,1)+RIGHT(A1,3)-1

Reply | Quote