Subtracting valid Sheets dates gives an elapsed day count. A date-looking text string needs separate interpretation before that arithmetic is reliable.
Check the inputs
Inspect the start and end cells and test their numeric representation with ISNUMBER. Sheets stores dates as serial numbers, so a date format alone does not prove that an imported string became a date value.
Confirm the intended calendar dates against the source. Numeric values can still represent dates that were misread during an ambiguous month/day import.
Calculate the elapsed difference
With a valid start date in A2 and end date in B2, =B2-A2 gives elapsed days. For November 1 to November 3, the difference is 2. Format the result as a number rather than a date.
If the business question counts both calendar endpoints, that is a different policy and may require an additional day for an ordered date-only interval. Working-day counts and partial-day timestamps require their own explicit treatment.
Test boundaries and missing records
Check equal dates, an end before the start, and a missing input. Do not let a blank date silently become an old serial-date origin in a report. Keep reversed dates visible unless negative elapsed time is intentional.
For text inputs, use a reviewed parsing method or construct dates from known numeric components. Preserve the raw source and avoid relying on ambiguous slash-separated text. If timestamps include times, subtraction can return fractional days; decide whether the report needs exact duration or calendar-day boundaries instead of rounding away a difference whose meaning has not been established.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.