Julian Type Date Conversion to Excel

[jmunsey]

Hi,

I have some raw data from an application, that has the date as follows:

108130

where 1 is century: that is, 21st century
where 08 is the year: that is, 2008
where 130 is the day in the given year: that is, May 9th

I am trying to find out how to convert 108130 to a date in excel
like:

5/9/2008

If anyone has ideas on this, it would be greatly appreciated.

Thanks,
Jeff

 

[DonkeyOte]

assuming you always have leading zeroes for each part of the data (ie 08 for month 8, and 011 for days) you could use the below (assuming A1 = your julian date)

=DATE(1900+(100*LEFT(A1,1))+(MID(A1,2,2)),1,1)+(RIGHT(A1,3)-1)

there's probably a *real* formula out there to handle these date types but I haven't check yet...

check out the below -- a very useful site...

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

 

[Yogi Anand]

Hi,

I have some raw data from an application, that has the date as follows:

108130

where 1 is century: that is, 21st century
where 08 is the year: that is, 2008
where 130 is the day in the given year: that is, May 9th

I am trying to find out how to convert 108130 to a date in excel
like:

5/9/2008

If anyone has ideas on this, it would be greatly appreciated.

Thanks,
Jeff

Hi Jeff:

How about ...

=DATE(1900+LEFT(A1,3),1,RIGHT(A1,3))

 

[DonkeyOte]

Doh!