DATEDIF
Count complete days, months or years from one date to another.
Syntax
DATEDIF(start_date, end_date, unit)| Argument | Required | Description |
|---|---|---|
start_date | Required | The earlier date. Must not be later than the end date. |
end_date | Required | The later date. |
unit | Required | "d" for days, "m" for complete months, "y" for complete years. |
Examples
Every example below was executed against VisiGrid engine 0.35.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=DATEDIF(DATE(2024,1,1),DATE(2024,3,15),"d") | 74 | |
=DATEDIF(DATE(2024,1,1),DATE(2024,3,15),"m") | 2 | Complete months. The extra fortnight does not count. |
=DATEDIF(DATE(2024,1,1),DATE(2025,3,15),"y") | 1 | Complete years — this is how ages are calculated. |
=DATEDIF(DATE(2025,1,1),DATE(2024,1,1),"d") | #NUM! | The dates cannot be the wrong way round. |
It counts completed units
"y" between 1 January 2024 and 15 March 2025 is 1, not 1.2 and not 2. The
second year has not finished. That is exactly what an age is, and why
YEAR(end) - YEAR(start) gives the wrong answer for most of the year.
Same logic for "m": two complete months plus a fortnight is 2.
Order matters, and it errors
A reversed range returns #NUM! rather than a negative number. If the direction
is unknown, sort the arguments first:
=DATEDIF(MIN(A1,B1), MAX(A1,B1), "d")For plain day counts, subtracting is simpler and has no such restriction —
B1 - A1 gives days, negative if reversed. DATEDIF earns its place for months
and years, where subtraction has no meaning.
The undocumented one
DATEDIF is famously absent from Excel’s own function list despite working
there. It comes from Lotus 1-2-3 and was kept for compatibility. It works here
for the three common units.
Excel compatibility
Matches Excel for the d, m and y units, including #NUM! on a reversed range.