Rules
Part of Days between dates: six honest answers to one simple-looking pair
Days between dates formula: build the day number and test it
A days between dates formula built on a March-first day number, tested on leap days and century years, then reconciled with a spreadsheet's NETWORKDAYS.
Every date-difference formula is the same formula. Turn each date into a single integer, subtract. The only thing that varies between methods is how the integer is built, and the only thing that goes wrong is a leap day counted twice or not at all.
The construction below needs no leap-year branch. Each step is explained, and the result is tested on real dates, including the century years that trip up most methods.
What to take away
- Start each year on 1 March, so the leap day is always the last day of the year and never shifts a later date.
- Four steps turn a date into a day number. Subtract two day numbers and you have the exclusive calendar count.
- The 153 and 5 encode the month-length pattern, and the 400-year era carries the century rule. Neither needs a special case.
- Test on pairs that straddle February, a year boundary and the years 1900 and 2000.
- A spreadsheet reaches the same count by plain subtraction, and NETWORKDAYS then differs by exactly one when the start is a working day.
Why the year starts in March
Moving the year start to March puts the leap day at the end of the year instead of the 59th day. Every date after it is then unaffected by whether the year is leap, and the formula needs no branch.
That one shift is what removes the usual bug. A method that tests for a leap year has to decide, for every pair of dates, whether the extra day lies between them. Here the extra day sits at the edge of a shifted year, so the decision never arises.
Build the day number
Take a date as year y, month m (1 to 12), day d. Shift the year so that March is the first month:
Day number in four steps
- Shift yearJan/Feb -> month +12, year -1
- Day of shifted yearfloor((153*(m-3)+2)/5)+d-1
- Days from cycle startyoe*365 + floors
- Day numberera*146097 + doe
- If
mis January or February, subtract 1 fromyand add 12 tom. February is now month 14 of the previous year and the leap day, if any, is the year's last day. - Day of the shifted year
doy = floor((153 * (m - 3) + 2) / 5) + d - 1. The 153 and 5 encode the pattern of 31 and 30-day months from March to January, which repeats every five months. - Days from the start of the 400-year cycle
doe = yoe * 365 + floor(yoe / 4) - floor(yoe / 100) + doy, whereyoe = y mod 400. - Day number
era * 146097 + doe, whereera = floor(y / 400)and 146097 is the number of days in 400 years.
Subtract two day numbers and the result is the exclusive calendar count between the dates. The same count can be reached on paper with a month table, as the calculator method shows.
What each term does
Nothing in the construction ever asks whether a year is a leap year. The floor terms in step 3 account for every leap day inside the cycle, and step 1 moves the leap day to where it cannot affect a date after it.
| Term | Job |
|---|---|
| Month shift in step 1 | Makes March month 3 and puts the leap day last in the year |
floor((153 * (m - 3) + 2) / 5) | Counts the days before month m in the shifted year |
yoe * 365 | Counts every year of the cycle as 365 days |
floor(yoe / 4) | Adds one day for each fourth year |
floor(yoe / 100) | Takes back the day for each century year |
era * 146097 | Adds each whole 400-year cycle at 146097 days |
Test it on dates that break methods
A formula earns trust on the cases built to break it. Choose pairs that straddle February in a leap year and a common year, one that crosses a year boundary, and the two century years. Compare each day-number difference with a direct count.
| From | To | Day-number difference | Direct count |
|---|---|---|---|
| 10 February 2027 | 23 November 2027 | 286 | 286 |
| 31 January 2027 | 1 March 2027 | 29 | 29 |
| 20 December 2026 | 10 January 2027 | 21 | 21 |
| 1 February 2028 | 1 March 2028 | 29 | 29 |
| 28 February 2000 | 1 March 2000 | 2 | 2 |
| 28 February 1900 | 1 March 1900 | 1 | 1 |
The last two rows are the ones a method usually fails. The U.S. Naval Observatory's leap year rule makes years divisible by 4 leap years, except centurial years not divisible by 400. So 2000 has a leap day, and 1900 does not. The formula gets both without a special case because the century rule is inside the floor terms.
Formula vs direct count
From
- 10 Feb 2027
- 23 Nov 2027
- 31 Jan 2027
- 1 Mar 2027
- 20 Dec 2026
- 10 Jan 2027
- 1 Feb 2028
- 1 Mar 2028
- 28 Feb 2000
- 1 Mar 2000
- 28 Feb 1900
- 1 Mar 1900
To
- 10 Feb 2027
- 286
- 31 Jan 2027
- 29
- 20 Dec 2026
- 21
- 1 Feb 2028
- 29
- 28 Feb 2000
- 2
- 28 Feb 1900
- 1
Day-number difference
- 10 Feb 2027
- 286
- 31 Jan 2027
- 29
- 20 Dec 2026
- 21
- 1 Feb 2028
- 29
- 28 Feb 2000
- 2
- 28 Feb 1900
- 1
Direct count
- 10 Feb 2027
- 31 Jan 2027
- 20 Dec 2026
- 1 Feb 2028
- 28 Feb 2000
- 28 Feb 1900
The negative case is the last check. When the two dates are reversed, the day-number difference comes out negative. The sign tells you the order, and the magnitude is the count.
The same idea in astronomy and spreadsheets
A Julian day number is a continuous count of days from a fixed epoch, and differences between them are differences between dates. Spreadsheets do exactly this with a more recent origin, which is why subtracting two date cells works.
=B2-A2 is the day-number subtraction with the origin chosen for you. If either cell carries a time, wrap both in INT so the fractional part does not turn 3 days into 2.9.
Reconcile with NETWORKDAYS
The NETWORKDAYS function returns the number of whole working days between a start and an end date, excluding weekends and any holiday dates supplied. It counts both endpoints, so it relates to the day-number difference in one fixed way.
When the start date is a working day, NETWORKDAYS returns one more than a count that excludes the start. When the start is a rest day, the two are equal. The reconciliation is exact and always one.
The year gives a clean example. From Friday 1 January to Friday 31 December 2027 the exclusive count is 364 days, which is 52 whole weeks, and 260 working days. NETWORKDAYS adds the first day, a Friday, and returns 261, the year's weekday count. Why a year lands on 260, 261 or 262 is a separate question.
Where this sits
A single pair of dates can carry six readings, and which one a document means is under counting between two dates. The inverse operation, moving forward a number of working days rather than counting them, is under adding business days.
When the formula runs in code instead of a sheet, the serial-versus-text contract matters, and it is covered under spreadsheet and API tools. Closures come off afterwards: one for each that falls on a working day inside the span.
Common questions
Why shift the year to start in March?
So the leap day is the last day of the year rather than the 59th. Every date after it is then unaffected by whether the year is leap, and the formula needs no branch.
Is the day-number difference ever negative?
When the dates are reversed. The sign tells you the order; the magnitude is the count.
Is there a shorter way to get the weekday without the day number?
Several, and they were worked out long before computers. Zeller's congruence is the one most often implemented, and it needs nothing but the date and modular arithmetic.
What about dates before the Gregorian calendar?
The construction assumes proleptic Gregorian dates throughout. For historical dates recorded under a different calendar, convert first; the day number of a date that was never Gregorian is a fiction.
