Julian Date Conversions in Excel
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 industry "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 correct term for the "day of the year" is "Ordinal Date".
Ordinal Date conversions
Convert a standard Excel 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 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
For Julian Dates calculated as the number of days since Noon (UT), Jan 1, 4713 BC
Note that UT (Universal Time) is typically known as GMT (Greenwich Mean Time). Also, standard Excel dates correspond to the Gregorian Calendar (established in 1582).
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)/24or 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".
The Julian Date Calculator spreadsheet below includes Ordinal Date calendars and worksheets for performing Ordinal Date conversions and Julian Date conversions using the methods listed above. The Ordinal Date calendars list the day-of-the-year (in "ddd" format) corresponding to the chosen year in the Gregorian calendar (the calendar that Excel dates are based upon). It also calculates the zeroth Julian Date for the start of the year (i.e. 0:00 GMT, Jan-0-yyyy). The Julian Date (JD) for a specific day can be found by adding the day-of-the-year to the zeroth JD.
Julian Date Calculator in Excel
Screenshot |
This free Julian date calculator gives the Ordinal dates for an entire year for any year in the Gregorian calendar (after 1582) and includes worksheets for Ordinal Date conversion and Julian Date conversion. "No Installation, No Macros - Just a simple spreadsheet" |
Download Cost: Free ($0.00) License: Personal Use Only File Type: .xls Size: ~50 KB Version: 1.0 Required: Microsoft Excel® 2002(XP), 2003, 2007, 2010 |
Julian Calendar History
Below you will find a selection of references that describe the history of the Julian Calendar and the definitions of various terms. I would strongly encourage reading through these sources to get a better understanding of Julian dates and conversions between different calendars.
Related Resources and 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.
- Seidelmann, P. K. (Ed.). "Julian Date." ยง2.26 in Explanatory Supplement to the Astronomical Almanac. Mill Valley, CA: University Science Books, pp. 55-56, 1992.
- "Julian Dates," by Chip Pearson, http://www.cpearson.com/excel/jdates.htm, May 3, 2003.
- "How to work with serial number dates and Julian dates in Excel 2000," From Microsoft.com, http://support.microsoft.com/?kbid=214099
- "Method to Determine Whether a Year Is a Leap Year", From Microsoft.com, http://support.microsoft.com/?kbid=214019
- Julian Date Converter, From the U.S. Naval Observatory, http://aa.usno.navy.mil/data/docs/JulianDate.html
- "Insert Julian Dates," From Microsoft.com (Excel Online Help).