#### zip.survey

##### Board Regular
I have over 60,000 rows of data that is given in Julian date form. They look like this:

206031478
277152534
326451021

But I think only the first 3 numbers may be relevant in Julian form? How can I easily convert all 60,000 rows of Julian date format to normal dates?

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### HalfAce

##### MrExcel MVP
Hello zip,
In order to change julian dates to normal dates all you need do is format them to the
date format of choice.
I think there is a flaw in your assumption that only the first 3 numbers may be relevant
in Julian form. The julian dates 206, 277 & 326 would be July 24, Oct 3 and Nov 21 of
the year 0.
(Unless you're a real history buff, I think you'll want to use more than 3 digits.)

If it helps, the julian date for today (Oct. 19, 06) is the value 39009.

#### zip.survey

##### Board Regular
I was given the following numbers

206031478
277152534
326451021

and don't know how to make sense out of them.

#### HalfAce

##### MrExcel MVP

I was given the following numbers. . . and don't know how to make sense out of them.

What exactly were you asked to do with them?

#### Cloverken

##### Active Member
I did not know this myself but had to research it and I thought we should clarify about dates in Excel.

39009 would be the serial date of the Gregorian calendar for Oct 19,2006. Oct 19, 2006 would be the 39,009 day from Jan 1, 1900 which is day zero in the Excel calendar.

2454027.5 would be the julian date for Oct 19,2006 or 2454027.5 days since the start of the Julian Calendar day of Jan 1 4713 BC.

#### HalfAce

##### MrExcel MVP

Well I'll be danged!
All this time I thought we were working off the julian dates.
I knew we were counting from Jan. 1, 1900 but I didn't realize that was the Gregorian
calendar, or that the julian dates started in the year 4713 BC.
(Wouldn't even have though they were keeping track that far back!)
I guess that would indeed make a difference, even if only in the terminology.
(Thanks Cloverken, learned something new today. - and I imagine fairly important too!)

But,. . . if Oct 19, 2006 is actually 2454027 in the julian date, then 'dates' given
above would span from (roughly) 559,000 to 890,000 years in the future. (wouldn't they?)

#### Cloverken

##### Active Member
I did have to read this I did not know this. It really gets confusing quickly when talking about different calendars. The Gregorian Calendar was started in 1582 by Pope Gregory VIII to help correct the Julian calendar. But the calendar was not adopted by all countries at the same time. Therefore Microsoft thought they would avoid all the confusion of the older calendars and historical dates and start wtih the year 1900. But they did not take into account that in the Gregorian calendar a year divisible by 100 and not divisible by 400 does not have a leap year. Therefore in the year 1900 there is not a Feb 29th but if you type 60 into a cell in Excel and format it as a date, it will give you the date 2/29/1900, kind of funny.

looked at his julian numbers and can't quite figure out what they are. They could be dates far into the future. But also there are a few variations of the Julian calendar.
Modified Julian Day
Reduced Julian Day
Dublin Julian Day
Truncated Julian Day
Astonomists apparently adopted the Julian calendar for the ease of adding and subtracting dates far into the future or in history.

So I am done with my curiosity about calendars and decided that calendars are too confusing. I say lets forget about the past.

#### HalfAce

##### MrExcel MVP
Good detective work!
I never knew why (not enough interest to try & find out I guess) but I was aware that
excel (and/or Microsoft) thinks there was a Feb 29, 1900.

I say lets forget about the past.
I say this too sometimes! (Sorta depends on what I've just done.)

#### barry houdini

##### MrExcel MVP
I have over 60,000 rows of data that is given in Julian date form. They look like this:

206031478
277152534
326451021

But I think only the first 3 numbers may be relevant in Julian form? How can I easily convert all 60,000 rows of Julian date format to normal dates?

Do you know which dates these are supposed to represent? If, as you say, only the first 3 digits are relevant, so that the first one represents the 206th day of the current year then you could convert using

=DATE(YEAR(TODAY()),1,LEFT(A1,3))

Replies
1
Views
33
Replies
3
Views
175
Replies
3
Views
319
Replies
8
Views
108
Replies
9
Views
182

1,136,878
Messages
5,678,300
Members
419,753
Latest member
Sallylwy

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