365 or 366? Rather than hardcode it, compute the days in a year by subtracting consecutive Jan-1 dates — automatically correct for leap years.
The example
2026 has 365 days; 2028 has 366.
| A | B | |
|---|---|---|
| 1 | Year | Days |
| 2 | 2026 | 365 |
| 3 | 2028 | 366 |
The formula
Difference of consecutive new-year dates:
How it works
Let the calendar do the leap-year logic:
DATE(year, 1, 1)is January 1;DATE(year+1, 1, 1)is next January 1.- Their difference is the number of days in the year — no leap-year formula needed.
- A leap year returns 366 because February has 29 days; otherwise 365.
- To just test for a leap year:
=DAY(DATE(year, 2, 29)) = 29— TRUE if Feb 29 exists.
Why not hardcode 365? Day-count and interest calculations that assume 365 drift in leap years. Using the actual count keeps annualized rates and prorations exact. YEARFRAC with the right basis handles this for you in financial contexts.
Try it: interactive demo
Enter a year.
Variations
Is it a leap year?
TRUE/FALSE:
Days so far this year
Day of year:
Days remaining
To year-end:
Pitfalls & errors
Don’t assume 365. Leap years break hardcoded day counts in prorations and rates.
Result is a number. The subtraction returns a count, but if the cell is date-formatted it may show oddly — use General/Number.
Leap rules. Excel follows the standard Gregorian rule (and the 1900 leap-year quirk for legacy serials) — fine for modern dates.
Practice workbook
Frequently asked questions
How do I find the number of days in a year in Excel?
How do I test for a leap year?
Why not just use 365?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class