Days Between Two Dates in Excel: Formula and Traps
The formula for the number of days between two dates is the shortest one in Excel: subtract the earlier date from the later one. Nothing else is required, because Excel stores a date as a single number counting days.
The difficulties are all around the formula rather than in it — the cell format that disguises the answer, the text values that break the subtraction, and one genuine historical hole in the calendar Excel uses.
The Formula
With a start date in A2 and an end date in B2:
=B2-A2That is the whole calculation. It works because a date is stored as a serial number: 1 January 1900 is 1, 2 January 1900 is 2, and so on. Subtracting two serial numbers leaves the number of days that elapsed between them.
The same answer is available from DATEDIF(A2,B2,"D"), but the subtraction is preferable in a shared workbook: there is no hidden unit argument for someone else to misread.
The Common Mistake: The Answer Looks Like a Date
Enter the subtraction in a cell that already has a date format — which is what happens when you extend a column of dates — and Excel formats the result as a date too. A true difference of 4 days then displays as 5 January 1900, because day number 4 counted from the Excel epoch is 4 January plus the inherited offset.
The value is correct. Only the display is wrong. To fix it:
- Select the formula cells and press
Ctrl+1(orCmd+1on a Mac). - Choose General or Number instead of Date.
- Or use the Format dropdown on the Home tab to set the cells back to General before you enter the formula.
Any result that displays as a date in 1900 is almost certainly a correct day count wearing the wrong format — a 1900 date in a day-count column is the signature of this trap.
Worked Example: Four Days or Five?
Take 2026-09-01 as the start and 2026-09-05 as the end.
| Formula | Result | Reading |
|---|---|---|
=B2-A2 | 4 | Days elapsed — the standard interval |
=B2-A2+1 | 5 | Both end days counted as full days |
=DATEDIF(A2,B2,"D") | 4 | Same as plain subtraction |
Which of the first two you want depends on the question. A hotel stay from 1 to 5 September is four nights, so the plain difference is right. A five-day event that runs on the 1st, 2nd, 3rd, 4th and 5th covers five calendar days, so you add 1.
This single decision accounts for a large share of off-by-one disputes about deadlines and durations. Write down which convention the sheet uses, in the sheet, before anyone else opens it.
When the Subtraction Returns an Error
If the formula produces #VALUE!, one of the two cells is text rather than a real date. Test the column:
=ISNUMBER(A2)A FALSE means Excel is storing a string, and strings cannot be subtracted. Two repairs, depending on the source: use Data > Text to Columns > Finish to force the column into date format, or convert individual values with =DATEVALUE(A2).
Watch for the silent variant as well. A date typed as 03/09/2026 is genuinely ambiguous, and Excel resolves it by your regional settings rather than by intent. When both components are 12 or less, day and month can be swapped with no warning at all, which changes a day count by up to eleven months.
Where the Count Goes Wrong: 1900 Is Not a Leap Year
Excel reserves serial number 60 for 29 February 1900 — a date that never existed. 1900 was not a leap year: a century year is a leap year only when it is divisible by 400. Microsoft documents this as a deliberate compatibility decision inherited from Lotus 1-2-3, kept because fixing it would shift every date in every existing spreadsheet.
The practical consequence for day counting is narrow but real:
| Dates compared | Excel says | Actually |
|---|---|---|
| 28 Feb 1900 → 1 Mar 1900 | 2 days | 1 day |
| 1 Jan 1900 → 1 Mar 1900 | 60 days | 59 days |
| 1 Mar 1900 → any later date | Correct | Correct |
The last row is the reassurance. Every date from 1 March 1900 onward carries the same one-day offset, so the two errors cancel when you subtract one from the other: a normal modern day count is completely unaffected. The bug only shows up when a span straddles the phantom leap day.
Counting Working Days Instead of All Days
If weekends should not be counted, plain subtraction is the wrong tool:
=NETWORKDAYS(A2, B2)NETWORKDAYS excludes Saturdays and Sundays and counts both end dates, so a Monday-to-Friday week returns 5 where the subtraction returns 4. The full behaviour, including holiday lists and custom weekends, is on the business days in Excel page.
Counting Down to a Future Date
To see the days remaining until an event, subtract today from the target:
=A2 - TODAY()Before the event the result is positive; afterwards it turns negative, which is often more useful than an error. Wrap it if you want a friendlier label: =IF(A2-TODAY()<0, "Passed", A2-TODAY()).
Remember that TODAY() is volatile: it recalculates on every recalculation, so a countdown workbook always shows the current figure.
Datetime Values Change the Answer
If a cell contains a date and a time, the serial number carries a fraction, and subtracting two of them returns a fractional day. A span that looks like 2 days can compute as 1.75, because the endpoints fall at different times of day.
Either clear the time component with =INT(A2) before subtracting, or accept the fraction and format the result as a number with two decimals. Mixing the two conventions inside one column is a reliable way to produce a total that refuses to reconcile.
Count the days, weeks and months between any two dates in one step.
Open the Date Duration Calculator →Frequently Asked Questions
What is the formula for days between two dates?
Subtract the earlier date from the later one, for example =B2-A2. Excel stores dates as serial numbers, so the subtraction returns the elapsed number of days directly.
Why does my result show a date like 5 January 1900?
The cell has a date format. The value is the correct day count, but day number 5 is being displayed as a date five days after the Excel epoch. Set the format to General or Number.
Should I add 1 to the result?
Add 1 when both end dates should count as full days, such as the length of a multi-day event. Do not add 1 for intervals, such as the number of nights in a hotel stay.
Why does the subtraction return #VALUE!?
At least one cell contains text instead of a real date. Use ISNUMBER to find it, then convert the column with Text to Columns or DATEVALUE.
Does the count include the start date?
No. Plain subtraction measures elapsed days, so 1 September to 5 September is 4. The start day is not counted as a full day.
Does the 1900 leap year bug affect my result?
Only for spans that cross the phantom 29 February 1900. From 1 March 1900 onward every date carries the same offset, so differences between modern dates are exact.
How do I exclude weekends?
Use NETWORKDAYS instead of subtraction. It removes Saturdays and Sundays and counts both end dates, and it accepts a range of holiday dates as a third argument.
Why is my total slightly wrong when times are involved?
A cell holding both a date and a time stores a fraction as well as a whole number, so the subtraction returns a fractional day. Wrap the reference in INT to strip the time first.
Can I count down to a future date?
Yes. Subtract today from the target date, for example =A2-TODAY(). The result is positive before the date and negative after it.
Are the dates I enter on this site kept private?
Yes. Everything runs in your browser — nothing is sent to a server and nothing is saved.
Sources
- Microsoft Learn: Excel incorrectly assumes that the year 1900 is a leap year
- Microsoft Support: NETWORKDAYS function
- Wikipedia: Leap year