Excel Day of Year: Find the Day Number for Any Date

To find the day of the year in Excel, put a real Excel date in A2 and enter =INT(A2)-DATE(YEAR(A2),1,0) in another cell. For May 15, 2024, the result is 136: that date is the 136th day of a leap year.

What “day of the year” means

The day of the year, also called the ordinal day or day number, counts dates from January 1. January 1 is day 1, January 2 is day 2, and the count continues through December. The final number is 365 in a common year and 366 in a leap year. It is different from the day of the month (such as the 15 in May 15) and from the day of the week (such as Wednesday).

This count is useful when comparing dates across months, sorting events by their place within a year, or preparing reports that use an annual timeline. It also appears in some scientific, manufacturing, and data-processing systems. Excel does not have a built-in function named DAYOFYEAR, but a short calculation using its date values gives the same result for ordinary dates.

How the formula works

With a valid Excel date in A2, the formula =INT(A2)-DATE(YEAR(A2),1,0) subtracts the serial number for the day before January 1 of that date’s year. Excel stores dates as serial numbers, so subtracting one date serial from another gives the number of elapsed days. The INT function removes any time portion, ensuring that a timestamp still produces a whole-number day.

For example, if A2 contains May 15, 2024, the result is 136. The days before May total 121 in 2024: January has 31, February 29, March 31, and April 30. Adding the 15 days of May gives 121 + 15 = 136. The formula returns a number, not a date, so Excel can use it in further calculations, sorting, or charts.

DATE(YEAR(A2),1,0) means “the date with the same year as A2, month 1, and day 0.” Excel normalizes day 0 to the final day of the preceding month, December 31. The date in A2 is therefore one day after day 1, two days after day 2, and so on. This is why the formula’s result begins at 1 rather than 0.

Leap years and the 365-or-366 count

The formula automatically accounts for leap years because it uses the actual date serials and the year in A2. In a leap year, February 29 occupies a day in the count; dates after it are one number higher than they would be in a common year. For example, March 1, 2024 is day 61, while March 1, 2023 is day 60. The last day of 2024 is day 366, and the last day of 2023 is day 365.

A Gregorian calendar year is a leap year when it is divisible by 4, except that a year divisible by 100 is not a leap year unless it is also divisible by 400. Thus 2000 was a leap year, but 1900 was not; 2024 is a leap year, while 2100 will not be. You do not need to encode these rules in the day-of-year formula. Excel’s date arithmetic handles them for modern dates.

There is one historical Excel caveat: the default 1900 date system preserves a compatibility error that treats 1900 as if it had a February 29. Consequently, calculations around dates in 1900 follow Excel’s serial calendar rather than the historically correct Gregorian count. For typical business, personal, and scientific dates after 1900, this does not affect the formula’s result.

Enter the formula and fill it down

In Excel for Microsoft 365 on Windows, place dates in one column and the calculation beside them. For example, keep the dates in A2:A20 and use B2 for the result. After entering the formula, press Enter, then copy it down for the remaining rows. A relative reference to A2 adjusts automatically to A3, A4, and subsequent rows.

Check that the cells in column A contain dates Excel recognizes, rather than date-looking text.
Select B2 and enter =INT(A2)-DATE(YEAR(A2),1,0).
Press Enter and confirm that the result is a whole number such as 136, not a displayed date.
Use the fill handle at the lower-right corner of B2 to copy the formula down, or copy B2 and paste it into the destination cells.

If your data is an Excel table, entering the formula in the first result cell usually fills the calculated column automatically. When you add more table rows, Excel can extend the formula for you. Mac users can use the same formula and fill-handle approach; the formula itself does not differ. Ribbon locations and some keyboard shortcuts can vary by platform, but no special add-in is required.

Choose a result format that suits the report

The calculation should normally remain numeric. If Excel displays the result as a date, select the result cells and change the number format to General or Number from the Home tab. A date format may make a valid day number appear as an unrelated calendar date because Excel is interpreting the result as a date serial. Changing the display format does not change the underlying calculation.

For reports that require three digits, such as 001, 045, or 366, select the cells, open Format Cells, choose Custom, and enter the type 000. This keeps the underlying result numeric while displaying leading zeros. If you need a text label instead, with the day number in B2, use =TEXT(B2,"000"). That returns text, so it is less convenient for numeric sorting or arithmetic. The formula assumes B2 already contains the day-of-year number.

It can help to label the output column clearly, for example “Day of year” or “Ordinal day.” A heading such as “Day” alone may be confused with day of the month. If the workbook includes several years, keep the original date or a separate year column as well: day 60 occurs every year, but it represents different calendar dates depending on the year.

Alternative formula and related calculations

With a real date in A2, an equivalent formula is =INT(A2)-DATE(YEAR(A2),1,1)+1. It subtracts January 1 and adds 1 because January 1 itself must be counted as day 1. Both formulas return 136 for May 15, 2024. The version using day 0 is slightly shorter; the version using January 1 can be easier to explain because it names the year’s first date directly.

If you want the number of days in the date’s year rather than the date’s position within that year, calculate the difference between January 1 of consecutive years. With a date in A2, =DATE(YEAR(A2)+1,1,1)-DATE(YEAR(A2),1,1) returns 365 or 366. This answers a related but different question: it gives the year’s length, not the day number for A2.

For a day-of-week result, use Excel’s WEEKDAY function instead; it does not return the day of the year. Similarly, DAY(A2) returns the day of the month. Keeping these measures distinct prevents a common reporting error: a value of 15 from DAY(A2) for May 15 is not the ordinal day, which is 135 in a common year or 136 in a leap year.

Common problems and how to fix them

The result is a date: Change the result cell format to General or Number. The formula result is a day count, and the display format may be misleading.
The formula returns an error: Check that A2 contains a valid date value. A string imported from a file may look like a date but remain text. Convert it to a recognized date using Excel’s import or conversion tools, or correct the source data.
The result contains a decimal: The source probably includes a time, such as May 15, 2024 at 6:00 PM. The INT in the recommended formula discards the time. If using a version without it, add INT around the date value.
The count is off by one: Confirm that January 1 should count as day 1, not day 0, and verify that the source date is in the intended year. The recommended formula follows the usual ordinal convention where January 1 equals 1.
Dates shift between workbooks: Excel workbooks can use different date systems. Windows workbooks generally use the 1900 system; some older or transferred workbooks may use the 1904 system. Check the workbook’s date-system setting when dates appear shifted after copying between files.

In regional settings, Excel may display dates in a different order, such as day/month/year rather than month/day/year. That affects how a date is entered or shown, not the formula’s logic once the cell contains a genuine date. Formula argument separators can also vary with regional settings; the examples here use commas, as specified for standard Excel syntax.

Frequently Asked Questions

What is the Excel formula for day of year?
With a date in A2, use =INT(A2)-DATE(YEAR(A2),1,0). It returns 1 for January 1 and 365 or 366 for the last day of the year.

Does Excel have a DAYOFYEAR function?
No. Excel does not provide a built-in function named DAYOFYEAR. Subtracting the previous December 31 from the date gives the ordinal day.

How do I find the day number for today?
Enter =INT(TODAY())-DATE(YEAR(TODAY()),1,0) in a cell. It returns the current date’s day number and updates when the workbook recalculates on a later date. TODAY is a volatile function, so its result can refresh when Excel recalculates.

How do I show a day number with leading zeros?
Keep the result numeric and apply the custom number format 000 to its cell. The display then shows, for example, 045 while the stored value remains 45.

Why does March 1 have a different day number in leap years?
February has 29 days in a leap year, so each date from March 1 onward is one count higher than the same month and day in a common year. The formula accounts for that automatically.

When this calculation is useful

A day-of-year column makes it easier to compare seasonal timing across multiple years. For instance, if annual inspection dates are listed as calendar dates, their ordinal numbers show whether the inspection happened earlier or later in the year without requiring a month-by-month comparison. It can also be used as a chart axis, a grouping field, or an intermediate value in a larger analysis.

Keep the actual date alongside the ordinal result whenever possible. The number alone does not identify a unique date: day 100 occurs once in every year, and its calendar date depends on whether that year is a leap year. Retaining both fields preserves context while allowing the day number to serve the specific comparison or calculation you need.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.