Excel Julian Date Formula: Day of Year and More

Formulas & functions · Updated November 18, 2026

For a date in A2, use =INT(A2)-DATE(YEAR(A2),1,0) to return its day number within the year: January 1 is 1, and December 31 is 365 or 366. “Julian date” can also mean a year-and-day code such as 26002 or an astronomical Julian Day Number, so the right Excel formula depends on which meaning you need.

What “Julian date” means

In everyday spreadsheets, a Julian date often means an ordinal date: the day of the year, numbered consecutively from 1. For example, February 1 is day 32 in a common year, while March 1 is day 60 in a leap year. This is useful for production logs, inventory records, lab data, and schedules that refer to a year and day rather than a month and day.

A second common convention combines the year and ordinal day. A code such as 26002 can mean the second day of 2026, and 2024060 can mean the 60th day of 2024. These are often called Julian dates in business and manufacturing systems, though the format is more precisely a year-plus-day-of-year code.

There is also an astronomical Julian Date system. It counts days continuously from an ancient epoch and can include a decimal portion for the time of day. Astronomers and some scientific datasets use it to compare observations across calendars. It is not interchangeable with a day-of-year value or a YYDDD code.

Calculate the day of the year

With a genuine Excel date in A2, the opening formula subtracts the date immediately before January 1 of the same year. The INT function removes any time portion, so a value containing both a date and a time still returns a whole day number.

For example, if A2 contains February 29, 2024, the result is 60. If A2 contains March 1, 2024, the result is 61. In a non-leap year, March 1 is day 60. The formula adjusts automatically because Excel’s DATE and YEAR functions use the calendar year of the input.

To use it, enter a real date in A2, select another cell, type the formula, and press Enter. If your dates are in a different column or row, replace A2 with the relevant cell reference. You can fill the formula down a column using the fill handle or copy and paste it.

Build a YYDDD or YYYYDDD code

If a system expects a five-character YYDDD code, where YY is the final two digits of the year and DDD is the day number padded to three digits, use this formula with the date in A2:

With the date in A2, use =TEXT(A2,"yy")&TEXT(INT(A2)-DATE(YEAR(A2),1,0),"000"). For February 29, 2024, it returns the text 24060. The ampersand joins the two pieces, and the three-zero number format keeps day numbers such as 1 as 001.

For a four-digit year followed by the three-digit day number, use this formula with the date in A2:

With the date in A2, use =TEXT(A2,"yyyy")&TEXT(INT(A2)-DATE(YEAR(A2),1,0),"000"). For February 29, 2024, it returns 2024060. Both examples return text, which is generally suitable for an identifier or export field. Text also preserves leading zeroes in the day portion.

If you specifically need a numeric YYYYDDD value, with the date in A2 use =YEAR(A2)*1000+INT(A2)-DATE(YEAR(A2),1,0). For that same leap-day example, the numeric result is 2024060. A numeric value can be convenient for calculations, but it may be reformatted or displayed in scientific notation in some circumstances; choose text when the code is an identifier.

Leap years and day-number checks

A leap year has 366 days, so the final day number can be 366. Excel’s date arithmetic accounts for February 29 without needing a separate leap-year test. That is one reason to calculate the ordinal day from the date rather than maintain a hand-entered list of month lengths.

Useful checks include:

  • January 1 always returns 1.
  • February 28 returns 59 in both leap and common years.
  • February 29 returns 60 when it exists.
  • March 1 returns 60 in a common year and 61 in a leap year.
  • December 31 returns 365 or 366, depending on the year.

These checks can help spot a wrong reference, text that only looks like a date, or an off-by-one formula. If a receiving system expects day numbers from 0 through 365 instead of 1 through 366, confirm its specification: that is a different convention, and the standard ordinal date formula deliberately starts at 1.

Convert to an astronomical Julian Day Number

For a calendar date in A2, this formula returns the integer Julian Day Number associated with that civil date:

With the date in A2, use =INT(A2)-DATE(2000,1,1)+2451545. For January 1, 2000, the result is 2451545. The reference date makes the calculation easy to understand: January 1, 2000 has Julian Day Number 2451545, and dates are counted forward or backward from it.

Astronomical Julian Date convention changes at noon, not midnight. To return the Julian Date at the date’s time of day, use this formula with an Excel date or date-time in A2:

With the date or date-time in A2, use =A2-DATE(2000,1,1)+2451544.5. At midnight on January 1, 2000, it returns 2451544.5; at noon that day, it returns 2451545. If A2 contains a time, Excel stores it as a fraction of a day, and the formula carries that fraction into the result.

Do not confuse the astronomical Julian Date with an ordinal day such as 60 or a compact code such as 24060. They have different scales and purposes. Also, the formulas above assume Excel’s standard 1900 date system and dates on or after the reference date range where the date values behave normally; workbooks configured for the 1904 date system need special care when using fixed serial-date offsets. The ordinal-day formula based on DATE is not dependent on a fixed epoch.

Format and troubleshoot the result

The day-of-year formula returns a number. If Excel displays it as a date, select the result cell and choose Home > Number Format, then select General or Number. For a YYDDD code returned by TEXT, the result is already text and will not behave like a date in date arithmetic.

If the formula returns #VALUE! or an unexpected result, check the source cell first. A date may look correct while actually being text imported from a file. Try changing the cell’s number format to General: a real Excel date usually displays as a serial number. You can also re-enter the date or convert the imported values using Excel’s available data-import tools.

Regional settings can affect how a typed date such as 03/04/2026 is interpreted. It might mean March 4 or April 3. For reliable data exchange, verify the source format or use an unambiguous date convention. When formulas use function arguments, the examples here use commas, as in standard Microsoft 365 for Windows settings; some regional configurations use semicolons as argument separators.

If the result is one day too high, check whether the original cell includes a time and whether you used INT. If it is one day too low, check that the formula starts counting January 1 as day 1. A formula that subtracts January 1 itself without adding 1 will instead make January 1 equal zero.

Choose the formula that matches your data

Before sharing a result with another person or system, identify the expected convention and data type. A heading that simply says “Julian date” is not enough to settle it. Ask whether the required output is a day number, a code containing the year, or an astronomical value, and whether the receiver expects text or a number.

  • Use the ordinal formula when you need the day of the year for reporting or analysis.
  • Use a YYDDD or YYYYDDD text formula when a system requires a fixed-width year-and-day code.
  • Use the astronomical formula only when the dataset specifically calls for a Julian Day Number or Julian Date.
  • Keep the original Excel date in a separate column when possible; it is easier to verify and reformat than a code alone.

These formulas work in Microsoft 365 for Windows and use long-established functions. The date arithmetic is also available in Excel for Mac and Excel for the web; the main differences are interface details and regional formula separators. If dates are shared between workbooks, also consider whether either workbook uses the 1904 date system, which can affect stored date serials.

Frequently Asked Questions

What is the Excel formula for day of year?
With the date in A2, use =INT(A2)-DATE(YEAR(A2),1,0). It returns 1 for January 1 and automatically accounts for leap years.

How do I get a Julian date like 24060?
With a date in A2, combine its two-digit year with its three-digit ordinal day using =TEXT(A2,"yy")&TEXT(INT(A2)-DATE(YEAR(A2),1,0),"000"). February 29, 2024 returns 24060.

Does Excel have a JULIAN function?
No built-in Excel worksheet function named JULIAN converts a date to these common formats. Use date arithmetic with DATE, YEAR, and, when needed, TEXT.

Is a Julian date the same as a day-of-year number?
Not always. People use the phrase for both ordinal day or year-plus-day codes and the astronomical Julian Date system. Confirm the required format before sending or importing values.

Why does my Julian date show 60 or 366?
Those can be correct ordinal day numbers. In a leap year, February 29 is day 60 and December 31 is day 366. Check the input year and the receiving system’s counting convention.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.