Julian dates in Excel

Day-of-year (ordinal) Julian dates

These formulas assume a real Excel date sits in cell A1 (a value Excel stores as a serial number, not text). The core trick is that subtracting DATE(YEAR(A1),1,1) from the date gives the number of days since 1 January.

Day of year (1–366)

=A1-DATE(YEAR(A1),1,1)+1

Subtracting 1 January and adding 1 makes 1 January return 1 rather than 0. For 21 July 2026 this returns 202.

7-digit code (YYYYDDD)

You can build the number arithmetically, but the day-of-year part will not be zero-padded (day 5 would produce 2026005 only by luck of the maths):

=YEAR(A1)*1000 + (A1-DATE(YEAR(A1),1,1)+1)

The reliable, always-padded version concatenates text so single- and double-digit days still fill three positions:

=TEXT(YEAR(A1),"0000") & TEXT(A1-DATE(YEAR(A1),1,1)+1,"000")

5-digit code (YYDDD)

For the shorter two-digit-year form, take the last two digits of the year with RIGHT and pad the day of year to three places:

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

Convert an ordinal code back to a date

The reverse relies on a useful quirk of Excel’s DATE function: it happily accepts a day number larger than the length of the month and rolls the extra days forward. So passing the day-of-year as the “day” of January produces the correct calendar date. For a 7-digit code in A1:

=DATE(LEFT(A1,4), 1, RIGHT(A1,3))

DATE(2026, 1, 202) means “the 202nd day counting from 1 January 2026”, which Excel evaluates to 21 July 2026. For a 5-digit code you must supply the century yourself:

=DATE(2000+LEFT(A1,2), 1, RIGHT(A1,3))

Astronomical Julian Date (JD) and MJD

Excel stores dates as serial numbers where day 1 is 1 January 1900. The astronomical Julian Date is simply that serial number plus a fixed offset. With a date (or date-and-time) in A1:

=A1 + 2415018.5

Because Excel serials include the fractional time of day, this also handles hours and minutes automatically: 2026-07-21 12:00 returns 2461243.0. Remember the JD day begins at noon, so midnight lands on the .5.

The Modified Julian Date drops the leading millions and starts at midnight:

=A1 + 2415018.5 - 2400000.5      (i.e. =A1 + 15018)

To go from a Julian Date back to an Excel date, subtract the same offset (then format the cell as a date):

=A1 - 2415018.5

Worked example: 21 July 2026

A1:  2026-07-21   (a real Excel date, serial 46224)

Day of year        =A1-DATE(YEAR(A1),1,1)+1              -> 202
7-digit ordinal    =TEXT(YEAR(A1),"0000")&TEXT(202,"000") -> 2026202
5-digit ordinal    =RIGHT(YEAR(A1),2)&TEXT(202,"000")     -> 26202
Julian Date (JD)   =A1+2415018.5                          -> 2461242.5
Modified JD (MJD)  =A1+15018                              -> 61242

All five results come straight from worksheet functions — there are no macros, add-ins, or helper columns needed. Format the JD/MJD cells as Number (not Date) so Excel shows the raw count.