To find the day number of the year in Excel, subtract the first day of that year from the date and add 1: =A2-DATE(YEAR(A2),1,1)+1. If the date sits in cell A2, this formula returns an integer from 1 to 366 that represents how many days have passed since January 1, including the date itself. January 1 is day 1, February 1 is day 32, and December 31 is day 365 in a common year or day 366 in a leap year. The math works because Excel stores every date as a serial number counting up from January 1, 1900, so subtracting two dates gives the elapsed days between them. The DATE(YEAR(A2),1,1) part rebuilds this year's January 1 as a clean serial number, ready to be subtracted from the original date. Adding 1 fixes the inclusive count so that January 1 itself returns 1 instead of 0. The exact same shape works in Excel 2007, 2010, 2013, 2016, 2019, 2021, and Microsoft 365, and it also works unchanged in Google Sheets, LibreOffice Calc, and Apple Numbers.
What "Day Number of the Year" Actually Means
The day number of the year is the ordinal position of a date within its own year, counted from January 1. It is also called the ordinal date, and in logistics, aviation, and manufacturing you will often see it labeled as a "Julian date" on shipping labels, batch codes, and timestamp stamps. A code like 2026-187 compresses the year and the day-of-year into a single compact number: 2026 is the year, and 187 is the 187th day, which falls on July 6, 2026 in a common year. That same stamped number is what the ISO 8601 standard calls the ordinal date, written as YYYY-DDD with the day component padded to three digits so 2026-187 reads as cleanly as 2026-007.
It is important not to confuse this business-day ordinal with the astronomical Julian Day Number. The astronomical Julian Day Number is a continuous count of days starting from noon on January 1, 4713 BC, used by astronomers to compare epochs across millennia. The spreadsheet Julian date is just the day-of-year plus the year, and that is the only definition this article uses.
The Core Excel Formula, Step by Step
The single most reliable formula for the day number of the year in Excel is =A2-DATE(YEAR(A2),1,1)+1. It uses three built-in functions that exist in every Excel version: A2 is the cell holding the date, YEAR(A2) extracts the four-digit year, DATE(YEAR(A2),1,1) rebuilds January 1 of that year as a serial number, subtraction yields the days elapsed since January 1 (not counting January 1 itself), and the final +1 shifts the count so that January 1 returns 1.
Worked example for July 6, 2026 in cell A2:
- YEAR("2026-07-06") = 2026
- DATE(2026,1,1) = serial 46023 (the day-count for Jan 1, 2026)
- A2 = serial 46209 (the day-count for July 6, 2026)
- 46209 − 46023 = 186 days elapsed since January 1, not counting January 1
- 186 + 1 = 187
That matches the ISO 8601 ordinal: 2026-187 is July 6, 2026. The formula needs no special handling for leap years because the math is relative to January 1 of the same year, so February 29 simply falls between February 28 and March 1 just like every other day.
Apply the Formula Across a Column of Dates
Most Excel users need the day number for an entire list of dates, not just one. Here is how to apply the formula to a column of dates in a few clicks.
- Make sure your dates are real Excel dates (not text), in column A starting at A2. If a column shows left-aligned text, select the cells, choose Data > Text to Columns > Finish, and the values become real dates.
- Click cell B2 and type =A2-DATE(YEAR(A2),1,1)+1.
- Press Enter; cell B2 displays an integer between 1 and 366.
- Grab the small square (fill handle) at the bottom-right corner of B2 and drag it down to match the height of your date column.
- For today's date only, replace A2 with TODAY() and use =TODAY()-DATE(YEAR(TODAY()),1,1)+1.
If you also want the padded ISO ordinal date, wrap the formula in TEXT(...,"000") and concatenate the year: =YEAR(A2)&"-"&TEXT(A2-DATE(YEAR(A2),1,1)+1,"000"). This produces strings like 2026-187 that copy cleanly into spreadsheets and label software.
Getting the ISO 8601 Week Number
Day numbers and ISO week numbers are different things, and Excel has historically confused them. The ISO 8601 rule is that weeks start on Monday, and week 1 is the week containing the year's first Thursday (the week that also contains January 4). Because of that rule, January 1, 2021 sits in week 53 of 2020, and December 30 and 31, 2024 both sit in week 1 of 2025.
To get the ISO week number in Excel 2010 or later, use one of the formulas below. The first two deliver the ISO week, and the third is the day-of-year ordinal for comparison.
| Formula | Returns | Best for |
|---|---|---|
| =WEEKNUM(A2,21) | ISO 8601 week number (1–53) | Excel 2010, 2013, 2016, 2019, 2021, 365 |
| =ISOWEEKNUM(A2) | ISO 8601 week number (1–53) | Excel 2013 and later, Microsoft 365 |
| =A2-DATE(YEAR(A2),1,1)+1 | Day number of the year (1–366) | Ordinal / Julian-style date |
For the full ISO week date — year, week, and weekday — combine ISOWEEKNUM with WEEKDAY: =YEAR(A2+3-WEEKDAY(A2,2))&"-W"&TEXT(ISOWEEKNUM(A2),"00")&"-"&WEEKDAY(A2,2). The result for July 6, 2026 is 2026-W28-1, which matches the ISO 8601 standard exactly.
Handling Leap Years in the Formula
The day-of-year formula handles leap years automatically, but you often also want a flag that says "this year is a leap year." The full Gregorian leap-year rule is: a year is a leap year if it is divisible by 4, except for century years, which must also be divisible by 400. So 2000 was a leap year, but 1900 and 2100 were not. One short Excel formula that captures the rule is:
=IF(DAY(DATE(YEAR(A2),3,1)-1)=29,"Leap year","Common year")
The trick is that February 29 only exists in a leap year, so DATE(year, 3, 1) − 1 is the last day of February, and asking whether that day is the 29th tells you whether the year is a leap year. The resulting total of days in the year is either 365 (common) or 366 (leap), and the days remaining in the year is simply 365 or 366 minus the day number you just calculated. If you want the days remaining directly, the cleanest single-cell formula is =DATE(YEAR(A2)+1,1,1)-A2-1.
When the Day of Year Calculator Beats Excel
Excel is the right tool when you have a column of fifty dates to process or when the day number needs to live inside a larger workbook. For a one-off question such as "what day of the year is 2026-07-06?" opening a browser tab is faster than typing a formula. The Day of Year Calculator shows the day number, days remaining, ISO 8601 week number, total days in the year, and a leap-year flag all at once, and the result updates the moment you pick a date. Nothing is uploaded, so the date you enter never leaves your device. If you also need to convert a date into a different display format such as ISO 8601 or dd/mm/yyyy, the date-format conversion guide walks through the same Excel formulas side-by-side with an online checker.
Common Uses for Day-of-Year Numbers
Day-of-year numbers show up in more workflows than most people realize. Logistics and manufacturing labels often stamp batch codes such as 2026-187 so an inspector can read the date without a calendar. Scientific and engineering data sets use the day-of-year as a compact column for time-series analysis, since 1–366 numbers sort and chart more cleanly than full dates. European payroll, factory shift, and project-management schedules reference ISO 8601 weeks because week 1 always contains the first Thursday, so a "week 1" plan is unambiguous across borders. Personal goal-tracking and countdown apps often display "X days left in the year" as a motivational tagline. In every one of those cases, having the day number — and ideally the ISO week — at your fingertips removes the ambiguity that month-day pairs can introduce.
Excel Pitfalls to Watch For
Three classic mistakes trip up new users of the formula. First, dates that are actually text — values that look like 07/06/2026 but are left-aligned in the cell — break the subtraction. Wrap those in DATEVALUE(A2) first, or run Text to Columns to convert them into real dates. Second, the 1904 date system used by older Mac workbooks shifts every date by 1,462 days, so a formula that returns 187 on Windows may return a smaller number on a Mac saved in 1904 mode. Check File > Options > Advanced > When calculating this workbook if your numbers look off by four years. Third, ISO week numbers at year boundaries behave unexpectedly: January 1, 2021 is in week 53 of 2020, and December 31, 2024 is in week 1 of 2025. Always treat the ISO week-year as part of the result, not just the week number, when planning cross-border schedules.
Once you have the formula memorized, the day number of any date is one cell and one keystroke away, and the ISO week number is right next to it. For a quick check without opening a spreadsheet, the Day of Year Calculator gives you the same answer in a single click.