Date formula page
Days Between Dates Formula
Use date subtraction when both cells contain real spreadsheet dates and you want elapsed calendar days.
Calculate days between dates in Excel or Google Sheets.
If A2 is 2026-01-01 and B2 is 2026-01-31, the result is 30.
Copy formulas
=B2-A2=B2-A2Simple subtraction returns elapsed calendar days and excludes the start date; add 1 when the report needs both endpoints included.
Example data
| Start Date | End Date | Holiday | Result |
|---|---|---|---|
| 2026-01-01 | 2026-01-31 | 2026-01-19 | 30 |
| 2026-02-10 | 2026-03-12 | 30 | |
| 1990-05-20 | 2026-05-20 | 36 |
If A2 is 2026-01-01 and B2 is 2026-01-31, the result is 30.
How the formula works
- Spreadsheet dates are stored as serial numbers.
- Subtracting start from end returns elapsed days.
- Add 1 only when your business rule counts both endpoints.
| Syntax piece | Role in the formula |
|---|---|
| A2 | The start date stored as a spreadsheet date serial. |
| B2 | The end date stored as a spreadsheet date serial. |
| B2-A2 | The end-date-minus-start-date calculation for elapsed days. |
| +1 | The optional adjustment that includes both the start and end dates. |
Verified examples
=B2-A2Excel: Enter 2026-01-01 in A2 and 2026-01-31 in B2 as real dates. Returns: 30
=B2-A2+1Google Sheets: Use the same start and end dates in A2 and B2. Returns: 31
=B2-A2Excel: Enter 2026-01-31 in A2 and 2026-01-01 in B2. Returns: -30
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| The formula returns #VALUE! | One or both inputs are text that the spreadsheet cannot parse as dates. | Convert the inputs to real date values and check the locale-specific date format. |
| The result displays as a date | The result cell has date formatting instead of number formatting. | Format the result as General or Number to show the day count. |
| The result contains decimals | One or both cells include time values in addition to the dates. | Wrap the inputs with INT when time-of-day precision is not wanted. |
When not to use this formula
- Do not use simple subtraction when weekends, holidays, or business-day rules must be excluded; use a workday function instead.
Alternatives
| Alternative | When to use it |
|---|---|
| Workdays Between Dates Formula | Use when weekends or listed holidays should be excluded. |
| Date Difference Formula Builder | Use when the date difference needs guided unit and boundary choices. |
Related formulas
Official references
- Calculate the difference between two dates from Microsoft
FAQ
Does simple subtraction include both dates?
No. B2-A2 measures elapsed days between the dates; add 1 to count both endpoints.
What does a negative result mean?
It means the end date in B2 occurs before the start date in A2.