MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Julian Date


Posted by Craig Dunworth on February 16, 2000 2:18 PM

Is there a way to convert a long format date
02/16/2000 to a julian date.


Posted by Celia on February 16, 2000 3:57 PM


Craig
I found the following formula at http://www.cpearson.com/excel/jdates.htm

=RIGHT(YEAR(A1),2)&TEXT(A1-DATE(YEAR(A1),1,0),"000")

Celia


Posted by Mark on February 17, 2000 12:03 PM

Craig

Enter date in any Excel format in cellmA1 and in cell A2 enter

=2415019+A1

The result is the Julian date. Be sure cell A2 is in number format and not date format.

Mark

Posted by Celia on February 17, 2000 5:09 PM

Mark
This does not seem to produce the Julian date.
Is there something missing or am I doing something wrong?
Celia

Posted by Mark on February 18, 2000 6:32 AM


Hi Celia

Feb 18, 2000 in Excel 97 is 36,574 and adding 2,415,019 gives 2,451,593 for the Julian day.
This is the same result as found at http://aa.usno.navy.mil/AA/data/docs/JulianDate.html
for Julian day relative to the common era.
Mark