How do I count days in Excel or Google Sheets?
download.fan · Updated
Short answer
Subtract the dates: with the start in A2 and the end in B2, =B2-A2 gives the number of days. =DAYS(B2,A2) does the same with the end date first. For working days use =NETWORKDAYS(A2,B2), which skips weekends and can skip a holiday list. These formulas work in both Excel and Google Sheets.
Which formula should I use?
- =B2-A2: total days. It counts the start or the end, not both.
- =DAYS(B2,A2): the same result. Note that the end date comes first.
- =DATEDIF(A2,B2,"D"): the same result, with "M" or "Y" for complete months or years.
- =NETWORKDAYS(A2,B2): weekdays only, counting both the first and last date if they're weekdays.
- =NETWORKDAYS(A2,B2,E2:E10): weekdays only, skipping the holiday dates in E2:E10.
- =B2-TODAY(): days until a date, updated every time the sheet recalculates.
How do these formulas compare on a real example?
From November 4 to December 25, 2026, =B2-A2 returns 51. =NETWORKDAYS(A2,B2) returns 38, because December 25, 2026 is a Friday and both end dates count. Add 1 to the subtraction if you want both dates in the total: =B2-A2+1 gives 52.
To check a result without a spreadsheet, use this day counter as a count days calculator. Its default leaves out the end date, the same as plain subtraction, and an option includes it.
Why does my formula show a date or an error?
- It shows a date like 2/20/1900: the cell has a date format. Change it to Number or General.
- #VALUE!: one of the dates is text, not a real date. Re-enter it, or convert it with DATEVALUE.
- #NUM! from DATEDIF: the start date is after the end date, so swap them.
- A negative number from subtraction: the same problem, but subtraction reports it rather than failing.
Is DATEDIF safe to use?
For "D", "M" and "Y", yes. Microsoft warns against the "MD" unit, which can return negative or inaccurate results. It also doesn't appear in Excel's formula autocomplete, so you need to type it out in full.