Julian Calendar Date Conversion Formulas

There are three main uses of the term "Julian Date": (1) A date in the Julian Calendar (2) Another term for "Julian Day Number" which is the number of days since noon GMT on Jan 1, 4713 BC, and (3) A common date format used in the computer indusry "yyyyddd" or "yyddd".
Peter Meyer's article, "Julian Day Number" includes references suggesting that the third use should no longer be used and that the more official term for the "day of the year" is "Ordinal Date".
References:
Peter Meyer. "Julian Day Numbers." From http://hermetic.nofadz.com/cal_stud/jdn.htm
Eric W. Weisstein. "Julian Date." From MathWorld--A Wolfram Web Resource.
"Julian Day" From Wikipedia, http://en.wikipedia.org/wiki/Julian_day.
Eric W. Weisstein. "Julian Calendar." From MathWorld--A Wolfram Web Resource.

Ordinal Date conversions


"Ordinal Dates" in the form yyddd or yyyydd
Ref: http://support.microsoft.com/?kbid=214099

Convert a Standard Date to the date format "yyddd".
(Due to Y2K, this isn't a very safe format. I prefer "yyyyddd".)
=TEXT(xldate,"yy")&TEXT(xldate-DATE(YEAR(xldate),1,0),"000")


When using the format "yyyyddd", you can replace "yy" with "yyyy" in the above formula, or because there are no leading zeros, you don't have have to treat the Ordinal date as text, leading to the following formula:
=YEAR(xldate)*1000+xldate-DATE(YEAR(xldate),1,0)


Note that standard Excel dates (xldate) are only defined between Jan 1, 1900 and December 31, 9999, so formulas that use Excel's built-in date functions will likely only work in this range.

Convert a date in the form "yyddd" to a Standard Date.
This can be done using the following julian date conversion formula:
=DATE(century+INT(datetext/1000),1,MOD(datetext,1000))
where century is 1900, 2000, 2100, etc. and datetext is the date in the format "yyddd". A better way in my opinion is converting from the format "yyyyddd" which avoids having to use the century variable, and datetext can be numeric:
=DATE(INT(datetext/1000),1,MOD(datetext,1000))


Julian Date (Julian Day Number) Conversions


Julian Date calculated as # of Days Since Noon (UT), Jan 1, 4713 BC
Note that UT (Universal Time) is typically known as GMT (Greenwich Mean Time). Also, Excel dates correspond to the Gregorian Calendar (established in 1582).

References:
http://scienceworld.wolfram.com/astronomy/JulianDate.html
http://aa.usno.navy.mil/data/docs/JulianDate.html

The Excel help documentation suggests using the following conversion to calculate a Julian Date:
=DATE(Y,M,D)+2415018.5
where Y is the year, M is the month, and D is the day, and DATE(Y,M,D) could be replaced with just a standard Excel date. The problem is that this only works for years after 1901.

Using the formula from scienceworld.wolfram.com, the Julian Date for any date in the Gregorian calendar (at 0:00 GMT) can be calculated using:
=367*Y - INT(7*(Y + INT((M+9)/12))/4) 
- INT(3*(INT((Y+(M-9)/7)/100)+1)/4) 
+ INT(275*M/9) + D + 1721028.5
Keep in mind that the Gregorian calendar starts on 15-Oct-1582.

To include the TIME in the Julian Date conversion, you can use a couple of different formulas to add in the day-fraction for a time specified as HH:MM:SS (24-hour Greenwich Mean Time):
=(HH+(MM+SS/60)/60)/24
or simply
=xltime
where xltime refers to a cell containing a time value. The second formula works because Excel stores time values as a fraction of a day so that (noon=0.5). Note that in order to display the day-fraction, you need to change the format of the cell to general or number.

4 comments:

Anonymous said...

You have confused Julian date with Julian Day Number (JDN).
A Julian Date would be a month/day/year in the Julian calendar, still in use in some Eastern Orthodox communities.
A JDN would be the day count since 4713 BC. (Plus 0.5 if you are an astronomer.)
It's bad enough that people confuse serial day number with Julian Day Number.

Jon Wittwer said...

I've modified the article. Hopefully, it is less confusing and more accurate now.

Anonymous said...

Two excel functions that work for dates less than around +15000101 (thats 1 Jan 1500 AD)

********* cut here *************
Function jymd(ymd As String)
'Keith @ xylem.demon.co.uk
'http://www.xylem.demon.co.uk/kepler/
'modified to take -yyyymmdd (9 characters) if input (8 characters) yyyymmdd assumed positive
'note "-" years only convert using jday(yyyy/yyyyy,mm,dd)
'=(IF(LEN(C6)=9,jday(LEFT(C6,5),MID(C6,6,2),RIGHT(C6,2)),jday(LEFT(C6,4),MID(C6,5,2),RIGHT(C6,2))))
' Keith's origional was
'Function jday(year As Integer, month As Integer, day As Integer, _
'hour As Integer, min As Integer, sec As Double, Optional greg) _
'As Double
Dim year As Integer
Dim month As Integer
Dim day As Integer
Dim hour As Integer
Dim minute As Integer
Dim sec As Double
Dim greg As Integer
Ll = Len(ymd)
ylen = Ll - 4
year = Left(ymd, ylen): month = Left((Right(ymd, 4)), 2): day = Right(ymd, 2)
If year < 0 Then year = year + 1
'If Len(ymd) > 0 Then year = (Left(ymd, 4)): month = (Mid(ymd, 5, 2)): day = (Right(ymd, 2)): GoTo 110
'If ymd < 0 Then year = (Left(ymd, 5)): month = (Mid(ymd, 6, 2)): day = (Right(ymd, 2))
'110 ' catch above
'Debug.Print Ll; " "; ylen; " "; ymd; " "; year; " "; month; " "; day
hour = 0
minute = 0
sec = 0
greg = 1
Dim a As Double
Dim b As Integer
a = 10000# * year + 100# * month + day
If (a < -47120101) Then
MsgBox "Warning: date too early for algorithm"
End If
'If (IsMissing(greg)) Then greg = 1
If (month <= 2) Then
month = month + 12
year = year - 1
End If
If (greg = 0) Then
b = -2 + Fix((year + 4716) \ 4) - 1179
Else
b = Fix(year \ 400) - Fix(year \ 100) + Fix(year \ 4)
End If
a = 365# * year + 1720996.5
jymd = a + b + Fix(30.6001 * (month + 1))
jymd = ((jymd + day + (hour + min \ 60 + sec \ 3600) \ 24) + 0.2916)
'jymd = ((jymd + day + (hour + min / 60 + sec / 3600) / 24) + 0.5)
End Function
Function jxx(jd)
'adapted from code at http://webexhibits.org/calendars/calendar-christian.html
'cant handle - dates
'f = 1
'If f = 0 Then GoTo 500
'Gregorian "1 for f"
' note division is "\" not "/"
a = jd + 32044
b = (4 * a + 3) \ 146097
c = a - (b * 146097) \ 4
GoTo 600
'500 'Julian "0 for f"
'b = 0
'c = (jd + 32082)
600 'Gregorian and Julian
d = (4 * c + 3) \ 1461
e = (c - (1461 * d) \ 4)
m = (5 * e + 2) \ 153
yyyy = b * 100 + d - 4800 + m \ 10
If yyyy < 0 Then yf = "-" Else yf = "+"
yyyy = Abs(yyyy)
700
If Len(yyyy) = 3 Then yyyy = "0" & yyyy
If Len(yyyy) = 2 Then yyyy = "00" & yyyy
If Len(yyyy) = 1 Then yyyy = "000" & yyyy
'yyyy = yf & yyyy
mm = (m + 3 - 12 * (m \ 10))
If Len(mm) = 1 Then mm = "0" & mm
dd = Int(e - ((153 * m + 2) \ 5) + 1)
dd = dd + 1
If Len(dd) = 1 Then dd = "0" & dd
If yf = "-" Then yyyy = Abs(yyyy) + 1
jxx = yyyy & mm & dd
If Len(jxx) = 5 Then jxx = "000" & yyyy & mm & dd
If Len(jxx) = 6 Then jxx = "00" & yyyy & mm & dd
If Len(jxx) = 7 Then jxx = "0" & yyyy & mm & dd
If yyyy = "0000" Then yyyy = "0001"
jxx = yf & jxx
Debug.Print jxx, yyyy, mm, dd
'Debug.Print yyyy; " "; mm; " "; dd
End Function


************ end cut ************

sunwukong (at) povn (dot) com

Anonymous said...

We store date in JDE as 108315 (YYYDDD) in order to display it as 11/10/2008 in MS Access instead of MS Excel I tweaked the forumula in my query as follows
StandardDate: DateSerial((Int([108315]/1000)+1900),1,([108315] Mod 1000))

for this example I typed 108315 where the field name would typically go and StandardDate is just the field name I picked.

HTH,
Lance