# Thread: converting a (julian) number into a date format

1. ## Re: converting a number into a date format

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.

2. ## Re: converting a number into a date format

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.

3. ## converting a (julian) number into a date format

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?

4. ## Re: converting a (julian) number into a date format

Have a look at the <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q95/0/78.ASP>Microsoft's explanation of working with Julian dates</A>

5. ## Re: converting a (julian) number into a date format

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

6. ## Re: converting a (julian) number into a date format

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

7. ## Re: converting a (julian) number into a date format

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

<pre>=DATE(LEFT(A1,1)+MID(A1,2,1)*10+2000,1,1)+RIG HT(A1,3)-1
</pre>

8. ## Re: converting a (julian) number into a date format

This seemed to work! Thanks so much.

-Kelley

#### Posting Permissions

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