Julian Date in Excel: Day of the Year and YYDDD Codes
By ExcelReady TeamUpdated 6 min read
The short answer
In business, a Julian date usually means the day of the year (1 to 366) or a code like YYDDD. In Excel, get the day number with =A2-DATE(YEAR(A2),1,0). Build a YYDDD code with =TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000"). Turn a code back into a date with =DATE(2000+INT(A2/1000),1,MOD(A2,1000)).
Key takeaways
- The day of the year is the date minus day 0 of January: =A2-DATE(YEAR(A2),1,0).
- A YYDDD code joins a 2-digit year to a 3-digit day: 15 March 2024 is 24075.
- Use TEXT with "000" so day 5 becomes 005; the result is text, which keeps leading zeros.
- DATE(year,1,day) converts a day number back into a real date, because DATE rolls extra days into later months.
- The astronomers' Julian date is different: for 1900-system dates from March 1900 on, it is the date serial plus 2415018.5.
On this page+
A Julian date in Excel usually means one of two things: the day of the year (1 to 366), or a short code such as YYDDD that joins the year to that day number. Manufacturing lot codes, shipping labels, and older business and government systems use these codes. Excel has no single Julian date function, but two short formulas convert a normal date to a code and back. This guide shows both, plus the version astronomers use.
What does "Julian date" mean?#
The term is used for three different things, so check which one your system expects:
| Format | Example for 15 March 2024 | Where you see it |
|---|---|---|
| Day of the year | 75 | Planning, production schedules |
| YYDDD (5 digits) | 24075 | Lot codes, labels, older systems |
| YYYYDDD (7 digits) | 2024075 | Systems that need a 4-digit year |
| CYYDDD (6 digits) | 124075 | Some ERP systems; C is 0 for the 1900s, 1 for the 2000s |
| Astronomical Julian date | 2460384.5 | Astronomy and science |
The first four are really "ordinal dates": a year plus the day count within it. Day 1 is 1 January, and 31 December is day 365, or 366 in a leap year.
How to get the day of the year in Excel#
Subtract "day 0 of January" from the date. Excel stores dates as numbers, so subtracting two dates gives the days between them. With the date in A2:
=A2-DATE(YEAR(A2),1,0)YEAR(A2)takes the year from the date: 2024.DATE(2024,1,0)means day 0 of January, which Excel reads as 31 December 2023.- 15 March 2024 minus 31 December 2023 is 75.
Leap years are handled for you: 2024 has 29 February, so 15 March is day 75, while in 2025 it is day 74. If the result looks like a date, such as 15/03/1900, change the cell format to General.
To learn why subtracting dates works at all, read why Excel dates are numbers. The YEAR, MONTH and DAY functions guide covers the parts used here.
How to create a YYDDD Julian code#
Join a 2-digit year to a 3-digit day number. TEXT turns numbers into text in a set format:
=TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000")| Date in A2 | TEXT(A2,"yy") | Day as "000" | Result |
|---|---|---|---|
| 15 Mar 2024 | 24 | 075 | 24075 |
| 7 Jan 2025 | 25 | 007 | 25007 |
| 31 Dec 2024 | 24 | 366 | 24366 |
| 1 Feb 2005 | 05 | 032 | 05032 |
The "000" format pads the day to three digits, so day 7 becomes 007. The result is text, which matters for years like 2005: as text, "05032" keeps its leading zero. If you need a number instead, wrap the formula in VALUE, but 05032 will then show as 5032.
For a 4-digit year, use YEAR instead of the first TEXT:
=YEAR(A2)&TEXT(A2-DATE(YEAR(A2),1,0),"000")This returns 2024075. The TEXT function guide and the list of TEXT function format codes cover "yy", "000" and many more.
How to convert a Julian date back to a normal date#
Split the code into a year and a day number, then let DATE count forward from 1 January. For a 5-digit YYDDD code in A2:
=DATE(2000+INT(A2/1000),1,MOD(A2,1000))INT(A2/1000)takes the first two digits: 24075 / 1000 = 24.075, so INT gives 24.MOD(A2,1000)takes the last three digits: 75.DATE(2024,1,75)asks for "the 75th of January". DATE rolls the extra days into the next months and returns 15 March 2024.
Format the cell as a date to see it (Ctrl+Shift+# on Windows applies a date format; on a Mac use Format Cells, ⌘1). This version also works when the code is stored as a number and has lost a leading zero, such as 5032 for 2005: INT gives 5, so the year is 2005.
For other code lengths, change the year part:
| Code | Formula |
|---|---|
| YYYYDDD, like 2024075 | =DATE(INT(A2/1000),1,MOD(A2,1000)) |
| CYYDDD, like 124075 | =DATE(1900+INT(A2/1000),1,MOD(A2,1000)) |
| YYDDD from the 1900s, like 98123 | =DATE(1900+INT(A2/1000),1,MOD(A2,1000)) |
If the code arrives as text and Excel won't calculate with it, see how to convert text to a date for cleaning steps.
How to get the astronomical Julian date#
Astronomers count days continuously from a starting point in 4713 BC, and each day starts at noon, so midnight falls on .5. The whole-number version is called the Julian Day Number. For dates in Excel's standard 1900 date system, add a fixed offset:
=A2+2415018.5For 15 March 2024 at midnight, this returns 2460384.5. Add the time of day to A2 for a more precise value. The offset only works for dates from 1 March 1900 onward, because Excel's 1900 system treats 1900 as a leap year for compatibility with older spreadsheets. Workbooks that use the 1904 date system, which some older Mac files do, need a different offset: 2416480.5.
Common Julian date mistakes in Excel#
- Using the wrong "Julian". A 5-digit lot code and a 7-digit astronomy number are not interchangeable. Check the system's documentation.
- Losing leading zeros. A number like 05032 shows as 5032. Keep codes as text, or use the INT and MOD formula, which handles both.
- Forgetting the century. YYDDD can't tell 1924 from 2024. Choose 1900 or 2000 in the formula based on your data.
- A date result where you expected a number. Change the format to General.
- Typing day-of-year instead of calculating it. Formulas update when the date changes; typed numbers don't.
For more date tools, the Excel date functions overview lists EDATE, EOMONTH, NETWORKDAYS and others.
Practise it#
Julian codes combine two skills: treating dates as numbers and formatting numbers as text. Practise how Excel stores dates with DATE, YEAR, MONTH and DAY, then practise TEXT and VALUE: you type each formula and it checks it instantly. The exercise on this page builds a real YYDDD code.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Batch codes use the YYDDD Julian format. In D2, build the code for the date in B2: =TEXT(B2,"yy")&TEXT(B2-DATE(YEAR(B2),1,0),"000"), then press Enter.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Julian code | ||||
| 2 | Line 2 | ||||
| 3 | Line 1 | ||||
| 4 | |||||
| 5 | |||||
| 6 |
Tip: The first TEXT gives the 2-digit year (24). The second gives the day of the year with three digits (075). The & joins them into 24075.
Practise this in a lesson
Frequently asked questions
How do I convert a date to a Julian date in Excel?
For a YYDDD code, use =TEXT(A2,"yy")&TEXT(A2-DATE(YEAR(A2),1,0),"000"). For a 7-digit YYYYDDD code, use =YEAR(A2)&TEXT(A2-DATE(YEAR(A2),1,0),"000"). For just the day of the year, use =A2-DATE(YEAR(A2),1,0).
How do I convert a Julian date back to a normal date in Excel?
For a 5-digit code like 24075, use =DATE(2000+INT(A2/1000),1,MOD(A2,1000)) and format the cell as a date. For a 7-digit code like 2024075, use =DATE(INT(A2/1000),1,MOD(A2,1000)).
What is today's Julian date in Excel?
Use =TODAY()-DATE(YEAR(TODAY()),1,0) for the day of the year, or wrap it in the TEXT formula for a YYDDD code. The result changes every day, because TODAY always returns the current date.
Why does my day-of-year formula show a date instead of a number?
Excel sometimes copies the date format into the result. Change the cell's number format to General or Number, and the day count such as 75 appears.
Is a Julian date the same as the Julian calendar?
No. The Julian calendar is an old calendar system. In business, a Julian date is a day-of-year code based on the normal calendar, and in astronomy it is a running day count. Excel uses the normal Gregorian calendar.