Excel Ordinal Date Format: What It Is & How to Use It

Excel’s ordinal date format (sometimes called a Julian date) represents a date as the number of days since a starting point, such as January 1 of a given year. You can create it with a custom number format like yyyyddd or yy-ddd, or calculate it with formulas. This article explains what ordinal dates are, how to format and compute them in Excel, and how to convert them back to regular dates.

What Is an Ordinal Date?

An ordinal date is a date format that combines the year with the day of the year (1 to 365, or 366 in a leap year). For example, January 1, 2023 is ordinal date 2023001, and December 31, 2023 is 2023365. This format is often called a Julian date, though true Julian dates (used in astronomy) count days since January 1, 4713 BC. In business and computing, the term “Julian date� usually refers to the ordinal format.

Ordinal dates are compact, sortable, and easy to compare, which makes them useful in inventory systems, manufacturing (for lot numbers), and data exchange between systems that don’t handle standard date formats well.

How Excel Stores Dates

Excel stores dates as serial numbers. For Windows, serial number 1 is January 1, 1900. For Mac (since 2011), serial number 1 is January 1, 1904. This difference matters when converting ordinal dates to serial numbers, but the ordinal format itself (year + day-of-year) is the same on both platforms.

To see a date’s serial number, type a date in a cell and change the number format to “General.� For example, January 1, 2023 is serial 44927 in Windows Excel. The day-of-year is simply the serial number minus the serial number of December 31 of the previous year.

Creating an Ordinal Date with a Custom Format

If you have a date in a cell and want to display it as an ordinal date, you can use a custom number format. Here’s how:

1. Select the cell or range with dates.
2. Press Ctrl+1 (Windows) or Cmd+1 (Mac) to open Format Cells.
3. Go to the Number tab, choose Custom.
4. In the “Type� box, enter one of these formats:

• yyyyddd – displays as 2023001 (4-digit year, 3-digit day)
• yy-ddd – displays as 23-001 (2-digit year, 3-digit day)
• yyyy"-"ddd – displays as 2023-001 (with a dash)

Click OK. The cell still contains the original date; only the display changes. This is the simplest way to get an ordinal date, and it works for any date in Excel’s range (January 1, 1900 to December 31, 9999).

Calculating Ordinal Dates with Formulas

If you need the ordinal date as text (for concatenation or export), use a formula. The day-of-year is the date minus the last day of the previous year:

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

Here, DATE(YEAR(A1),1,0) returns the last day of December of the previous year (day 0 of January). Subtracting that from the date gives the day number. TEXT(...,"000") pads it to three digits. The result is text, so it won’t be recognized as a date by Excel.

If you want a numeric value (e.g., 2023001 as a number), use:

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

This works for dates after 1900, but for dates before 1900 (which Excel doesn’t support), you’d need a different approach.

Converting an Ordinal Date Back to a Normal Date

To convert an ordinal date like 2023001 back to a regular date, you need to extract the year and day number. If the ordinal is text in cell A1:

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

This takes the first 4 characters as the year, and the last 3 as the day of the year. DATE builds a date from year, month, and day – but here the day is the day-of-year, so January 1 + day-1 gives the correct date. For example, 2023001 becomes January 1, 2023.

If the ordinal is a number (e.g., 2023001), use:

=DATE(INT(A1/1000),1,MOD(A1,1000))

This divides by 1000 to get the year, and the remainder is the day number. For ordinal dates with a 2-digit year (like 23001), adjust the extraction accordingly.

Common Mistakes and Pitfalls

• Confusing ordinal dates with true Julian dates – Astronomical Julian dates are a continuous count of days since 4713 BC, not year-specific. Don’t use them interchangeably.
• Leap years – February 29 adds a day, so the day-of-year for March 1 is 61 in a leap year, but 60 in a non-leap year. Excel’s DATE function handles this automatically, but manual calculations must account for it.
• Text vs. number – A custom format keeps the date as a real date, but formulas that return text cannot be used in date arithmetic. If you need to do calculations, keep the original date column.
• 1904 date system – On Mac, the serial numbers differ, but the ordinal formula using YEAR and DATE still works because it uses Excel’s date functions, not raw serial numbers.

Alternatives to Ordinal Dates

If you need a compact date representation for sorting or comparison, consider these alternatives:

• ISO 8601 format – YYYY-MM-DD is unambiguous and sorts correctly as text. Excel can display it with a custom format yyyy-mm-dd.
• Excel serial number – The underlying serial number is already sortable and usable in calculations, though not human-readable.
• Unix timestamp – Seconds since January 1, 1970, used in many programming contexts. Excel can convert with =(A1/86400)+DATE(1970,1,1).

Ordinal dates are most useful when you need a short, year-inclusive identifier that doesn’t contain month names or separators, such as in file names or barcode data.

Frequently Asked Questions

What is the difference between a Julian date and an ordinal date?

In Excel, “Julian date� often means the ordinal format (year + day of year), but true Julian dates are a continuous day count since 4713 BC. In astronomy, the Julian date for January 1, 2023 is 2459945.5. Excel’s documentation and many business contexts use “Julian� to mean ordinal, so check which one your data requires.

How do I get the day of the year in Excel?

Use =A1-DATE(YEAR(A1),1,0) where A1 contains a date. This returns the day number (1-365/366). For example, February 1, 2023 gives 32.

Can I sort ordinal dates in Excel?

Yes, if they are stored as numbers (like 2023001) or as text with a consistent format (like 2023-001). Text with a 4-digit year and 3-digit day sorts correctly because the lexicographic order matches chronological order. If you use a 2-digit year, sorting will fail after 2099.

Does the ordinal date format work for dates before 1900?

No. Excel’s date system starts at January 1, 1900 (Windows) or 1904 (Mac). Dates before that are not supported, so you cannot use YEAR or DATE with them. You’d need to treat them as text or use a different program.

Why does my formula return a #VALUE! error?

This usually happens when the cell contains text that looks like a date, or when you use LEFT and RIGHT on a number without converting it to text first. For a numeric ordinal, use INT and MOD instead, or wrap the number in TEXT(A1,"0000") before extracting.

Is there a built-in Excel function for ordinal dates?

No. Excel doesn’t have a dedicated function, but the custom format yyyyddd gives a display-only ordinal date, and the formulas above create a text or numeric version. For a full Julian date conversion (like to astronomical Julian Day), you’d need a custom formula or add-in.

Related

• File Can’t Be Found Mac Startup? Fix It Now
• Tint iPhone Home Screen Icons to Match Your Case in iOS 26
• Travel Cases for Apple Watch: What to Know

in d | Date (as serial number) | - | 45000 | 1
out ord | Ordinal date (yyyyddd) | - | TEXT(d,"yyyy") & TEXT(d-DATE(YEAR(d),1,0),"000") | 0