How to convert Julian dates and get the day of year in Excel

Day of the year from a date

DATE(YEAR(A2),1,0) asks for "day 0 of January", which Excel rolls back to 31 December of the year before. Subtract that from the date and you get its position in the year: 1 for 1 January, 45 for 14 February. =A2-DATE(YEAR(A2),1,1)+1 gives the same answer if you find it easier to read.

Leap years are handled for you, because Excel works from the real calendar. 1 March is day 60 in 2026 but day 61 in 2024, and 31 December 2024 is day 366. Never hard-code a table of month offsets; it is wrong one year in four.

If the answer shows as a date such as 14/02/1900, Excel has copied the date format from A2. Change the cell to General (Home > Number Format > General). For the current day of the year, use =TODAY()-DATE(YEAR(TODAY()),1,0).

Days left in the year, and days in the year

The same idea works from the other end. =DATE(YEAR(A2),12,31)-A2 counts the days still to come after the date: 320 after 14 February 2026. That is handy for pro-rating an annual fee or a leave allowance.

To know whether a year has 365 or 366 days, subtract 1 January from the next 1 January: =DATE(YEAR(A3)+1,1,1)-DATE(YEAR(A3),1,1) returns 366 for 2024. Use this instead of dividing by 365 when the exact year length matters.

Date to a YYDDD or YYYYDDD Julian code

In food packing, pharma, the military and many warehouses, a "Julian date" just means year plus day of year: 26045 is the 45th day of 2026. Excel has no format code for the day of the year, so you build the code with TEXT.

The "000" format is the part people miss. Without it, 5 January 2027 becomes "27" & 5 = "275", which reads back as day 275 of 2027. TEXT(...,"000") pads the day to three digits, giving 27005. For the 7-digit YYYYDDD form, swap TEXT(A2,"yy") for YEAR(A2): =YEAR(A2)&TEXT(A2-DATE(YEAR(A2),1,0),"000") returns 2026045.

The result is text, which is what you want for a code: it keeps every digit and lines up with the label printer. If a system needs it as a number, wrap it in VALUE.

Julian code back to a date

DATE accepts a day number larger than the month has, and rolls forward: DATE(2026,1,45) is 14 February 2026. So a code converts in one step. LEFT takes the year digits and RIGHT the day digits: =DATE(2000+LEFT(B2,2),1,RIGHT(B2,3)) returns Saturday 14 February 2026. For a 7-digit code use =DATE(LEFT(C4,4),1,RIGHT(C4,3)), which turns 2026305 into 1 November 2026.

Do not leave out the 2000+. DATE treats a year from 0 to 1899 as an offset from 1900, so DATE(26,1,45) is 14 February 1926 and the 27005 lot becomes 5 January 1927, with no error. For codes from the 1990s, decide on a cut-off, for example =DATE(IF(LEFT(B2,2)+0>50,1900,2000)+LEFT(B2,2),1,RIGHT(B2,3)).

Check the day part too. Day 366 only exists in leap years: 24366 is 31 December 2024, but a typo like 25366 quietly becomes 1 January 2026. To catch it, test that YEAR of the result still matches the year in the code.

Codes that lost their leading zero (and JDE dates)

Codes from 2000 to 2009 start with 0, and Excel drops it as soon as the column is a number: "05032" (1 February 2005) arrives from a CSV as 5032. LEFT(5032,2) is then "50", and the formula above returns 1 February 2050. The arithmetic version does not care about digits: INT(B5/1000) is the year part and MOD(B5,1000) is the day part, so it works on text codes, numbers and lost zeros alike.

JD Edwards (JDE) and some AS/400 systems store dates as CYYDDD, where C is the century: 0 for the 1900s, 1 for 2000 onwards. 126045 is 14 February 2026, and 31 December 1999 is 099365, which Excel shows as 99365. Because the century digit sits in front of the year, one formula covers both: =DATE(1900+INT(C2/1000),1,MOD(C2,1000)). Going the other way, =(YEAR(A2)-1900)*1000+A2-DATE(YEAR(A2),1,0) turns 14 February 2026 into 126045.

To keep codes intact on import, set the column to Text in the Text Import or Power Query step, or format the column as Text before you paste. To show a number with its zero again, use =TEXT(B5,"00000").

Astronomers' Julian Day is something else

Astronomy, satellite tracking and some scientific data use the Julian Day (JD): a running count of days since 4713 BC, with each day starting at noon UTC. It has nothing to do with YYDDD codes. 14 February 2026 at midnight UTC is JD 2461085.5.

Excel's date serial numbers are a running count too, so the conversion is a fixed offset for dates from 1 March 1900 onwards: JD = date + 2415018.5. Format the result as a number with one decimal, or Excel may show it as a date. For the whole-number Julian Day Number of the day, add 2415019 instead (2461086). For the Modified Julian Date used in many data files, add 15018 (61085). To go back, subtract the same offset and format as a date.

"Julian" can also mean the old Julian calendar, which runs 13 days behind ours. Excel has no function for that calendar, so if your source mentions it, check before you convert.