The day of the year is the ordinal position of a date within its calendar year, where January 1 is day 1 and December 31 is day 365 in a common year or day 366 in a leap year. In Excel there is no built-in DAYOFYEAR function, so you have to assemble one from pieces — usually a subtraction between the date in your cell and December 31 of the previous year. That single formula works for most rows, then breaks on the first of January, on blank cells, on text-formatted dates imported from another system, and on the corner cases that determine whether January 1 belongs to week 53 of the previous year or week 1 of the new one. If your goal is to find the day of year in Excel fast and without babysitting the formula across hundreds of rows, a free Day of Year Calculator covers the same job in one click: pick a date, read the day number, the days remaining, the ISO 8601 week number, and the leap-year flag, all updated instantly without a submit button. This guide walks through the Excel formulas for context, then shows how to switch to the calculator when the formula gets in the way.
The Day of Year, Defined
Every date in the Gregorian calendar sits at a fixed position between January 1 and December 31. That position, written as an integer from 1 to 365 (or 1 to 366 in a leap year), is the day of the year — also called the ordinal date. January 1 is day 1, February 1 is day 32, March 1 is day 60 in a common year and day 61 in a leap year, and December 31 is day 365 in a common year and day 366 in a leap year.
The same number shows up in different guises across industries. In logistics, aviation, and manufacturing it is often written as a Julian-style code that combines the two pieces of information you need from a date stamp: the year and the day number. ISO 8601 defines that combined code as the ordinal date, written YYYY-DDD with the day number padded to three digits — so July 6, 2026 becomes 2026-187, August 9, 2026 becomes 2026-221, and so on through day 365 or 366 on December 31.
One common confusion worth clearing up now: in business, "Julian date" usually means the ordinal day of the year. Astronomers use the same word for the Julian Day Number, which counts days continuously from 4713 BC and is a completely different system. The day-of-year calculator on this site gives the ordinal day of the year, the padded ISO ordinal date, and the ISO 8601 week number — not the astronomical Julian Day Number.
How Excel Calculates Day of Year
Excel stores dates as serial numbers (January 1, 1900 is day 1 in the Windows default 1900 date system, or January 1, 1904 in the legacy Mac 1904 system). Because every date is just an integer, the day-of-year number is the difference between a date and December 31 of the previous year. Excel has no native DAYOFYEAR, but four common patterns handle it.
| Approach | Formula | What it does |
|---|---|---|
| Subtract a day-zero date | =A2-DATE(YEAR(A2),1,0) | Builds Dec 31 of the previous year and subtracts it from the cell. Returns 1 on Jan 1, 365 or 366 on Dec 31. |
| Subtract the year's first day | =A2-DATE(YEAR(A2),1,1)+1 | Subtracts Jan 1 of the same year and adds 1, giving the same ordinal number without the day-zero trick. |
| Count days in the year | =DATE(YEAR(A2)+1,1,1)-DATE(YEAR(A2),1,1) | Returns total days in the year (365 or 366). Combine with subtraction to derive the day number. |
| Use DATEDIF | =DATEDIF(DATE(YEAR(A2),1,1),A2,"d")+1 | Counts whole days from Jan 1 to the date and adds 1, giving the ordinal day directly. |
Worked example for the first formula: with the date July 6, 2026 in cell A2, Excel evaluates DATE(YEAR(A2),1,0) as December 31, 2025. The serial numbers differ by exactly 187, so =A2-DATE(YEAR(A2),1,0) returns 187. That is the same number that appears in the ISO 8601 ordinal date 2026-187, confirming the formula is correct for any date you can put in a cell.
The formulas break in predictable places. A blank cell returns the serial number 0 and yields a nonsense answer near January 0, 1899. A cell formatted as text stays text, and subtraction throws #VALUE!. Imported dates from systems that use a different epoch may need a re-basing step. And none of the four formulas gives you the ISO 8601 week number, the days remaining, or the leap-year flag — those require separate work.
Skip the Formula With a Day of Year Calculator
For a single date, or for sanity-checking a column of Excel formulas against the official ordinal date, a calculator is the faster route. The Day of Year Calculator takes any date and returns four things at once: the day number (1–365 or 1–366), the days remaining in the year, the ISO 8601 week number with the full ISO week date, and whether the year is a leap year. Nothing is uploaded, nothing is stored, and there is no signup.
The tool handles the corner cases that Excel formulas typically fumble. The ISO 8601 week number follows the standard exactly: week 1 is the week that contains the year's first Thursday (equivalently, the week that contains January 4), weeks run Monday to Sunday, and the first few days of January can belong to week 52 or 53 of the previous year. Leap years use the full Gregorian rule — divisible by 4, except century years that must also be divisible by 400 — so 2000 is a leap year and 1900 and 2100 are not. The day number, the total days in the year, and the days remaining all stay consistent with those rules automatically.
Find Day of Year With the Calculator
- Pick any date in the date field — it defaults to today, so the day-of-year answer for today appears immediately.
- Read the day number of the year and the days remaining, updated the instant you change the date. There is no button to press.
- Check the ISO 8601 week number, the total days in the year, and whether it is a leap year in the panel below the day count.
- Copy either the plain day number or the padded ISO ordinal date (such as 2026-187) into your spreadsheet, label, or schedule, depending on what your workflow expects.
- Change the date to check another value — for batch dates, planning deadlines, or a quick leap-year test on a year like 1900 or 2100.
Reading the Output Fields
The day number is the plain ordinal you would write into a spreadsheet column. The padded ISO ordinal date combines the four-digit year with the day number, zero-padded to three digits, in the format YYYY-DDD — that is the form that appears on airline tickets, shipping manifests, and a lot of manufacturing batch stamps. Pick whichever one matches the field you are filling in.
The days remaining is just the total days in the year minus the day number, so it falls steadily through the year and lands at 0 on December 31. The total days in the year is 365 in a common year and 366 in a leap year, and the leap-year flag turns on exactly when that total is 366.
The ISO 8601 week number is the field that catches most ad-hoc scripts. ISO 8601 weeks start on Monday, week 1 is the week that contains the year's first Thursday (equivalently, the week containing January 4), and because of that rule the first days of January can sit in week 52 or 53 of the previous year while the last days of December can sit in week 1 of the next year. January 1, 2021 falls in week 53 of 2020; December 30 and December 31, 2024 both fall in week 1 of 2025. The full ISO week date, written 2026-W28-1, includes the ISO week-numbering year so those cross-boundary dates read correctly. If you are scheduling against European payroll, manufacturing weeks, or any ISO-based calendar, this is the field to use.
Day of Year vs Julian Date vs ISO Week
These three terms are easy to mix up because they all live on the same date but answer different questions.
- Day of year answers "which day of this year is it?" with a number from 1 to 365 or 366. It counts from January 1, not from Monday, and it does not care about weeks.
- Julian date (in logistics, aviation, and batch tracking) is the same number, often written as YYYY-DDD with the year in front. Astronomers use the same phrase for a very different continuous count from 4713 BC.
- ISO 8601 week number answers "which week of this ISO year is it?" with a number from 1 to 52 or 53. It runs Monday to Sunday, week 1 contains the year's first Thursday, and the ISO year is not always the same as the calendar year for dates near January 1 or December 31.
If your spreadsheet needs the day number for a Julian-style date stamp, use the day-of-year output. If your spreadsheet needs a week number for European payroll or manufacturing schedules, use the ISO 8601 week output. The calculator shows both, side by side, so you do not have to commit to one before picking the date.
When You Actually Need the Day of Year
Day-of-year numbers are everywhere once you start looking. They are the field you write into the day-of-year column on a shipping manifest, the three-digit suffix on an airline batch or repair tag, the value you fill into a scientific data sheet where the downstream tool expects an ordinal date, and the number you check when planning a deadline against "X days left in the year." They are also the easiest way to spot a misplaced date: if two batch stamps both show day 187 but one says 2026 and the other says 2025, you have a year typo and not a date typo.
For related date work, the same toolkit has dedicated calculators for the tasks that usually sit next to a day-of-year column. If you need to count the gap between two dates, a date difference calculator gives you whole days, weeks, and months in either direction. If you need the weekday for a specific date, a day of the week calculator returns Monday through Sunday instantly. Everything runs locally in the browser, so you can paste dates into the day-of-year tool, the date difference tool, the day-of-week tool, and an age calculator without ever sending a date to a server.