Date Conversion yyyy-365

[mhickey]

I have a speadsheet where the author entered dates with the combination
of the year plus the value of the calendar day value. I.E. December 31,
2005 is reflected as 2005365. Does anyone have a suggestion how to
convert this to a basic date format like mmddyyyy?

 

[bplumhoff]

Hello,

=TEXT(DATE(LEFT(A1,4),1,RIGHT(A1,LEN(A1)-4)),"MMDDYYYY")

HTH,
Bernd

 

[Franz Verga]

Nel post *[email protected]* ha scritto:

I have a speadsheet where the author entered dates with the
combination of the year plus the value of the calendar day value.
I.E. December 31, 2005 is reflected as 2005365. Does anyone have a
suggestion how to convert this to a basic date format like mmddyyyy?

This format of dating is called Julian date. You can check also on line help
for that.

If you have a Julian date in C19, using the formula:

=DATE(LEFT(C19,4),1,0)+RIGHT(C19,3)

you will have a date in your standard format.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

This is a variation of Julian date. See:

http://www.cpearson.com/excel/jdates.htm

Make adjustments to reflect 4-digit year rather than 2-digit

 

[Mike]

Thank you all. This did the trick and was very helpful.

Mike