How to convert Julian dates? Please help..

zip.survey

Board Regular
Joined
Sep 8, 2006
Messages
55
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
Joined
Apr 6, 2003
Messages
9,454
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.) :biggrin:

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

zip.survey

Board Regular
Joined
Sep 8, 2006
Messages
55
I was given the following numbers

206031478
277152534
326451021

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

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

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
Joined
Feb 18, 2002
Messages
271
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
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Well I'll be danged!
All this time I thought we were working off the julian dates. :confused:
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!) :LOL:
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?) :confused:
 

Cloverken

Active Member
Joined
Feb 18, 2002
Messages
271
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
Joined
Apr 6, 2003
Messages
9,454
Good detective work!
I never knew why (not enough interest to try & find out I guess) :oops: 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.) :LOL:
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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))
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top