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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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.
 
Upvote 0
I was given the following numbers

206031478
277152534
326451021

and don't know how to make sense out of them.
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top