# Julian Type Date Conversion to Excel

#### jmunsey

##### New Member
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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### DonkeyOte

##### MrExcel MVP
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

Last edited:

#### Yogi Anand

##### MrExcel MVP
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:

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

Doh!

Replies
3
Views
343
Replies
1
Views
602
Replies
4
Views
129
Replies
3
Views
143
Replies
4
Views
216

1,191,700
Messages
5,988,173
Members
440,135
Latest member
DCDavid

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back