DATEDIF
How long between two dates.
Measure a gap between two dates in whole years, months or days
A number, in whatever unit you asked for
Syntax
=DATEDIF(start, end, unit)
| Argument | Required | What goes here |
|---|---|---|
start | Required | The earlier date, which goes first. |
end | Required | The later date, which goes second. |
unit | Required | In double quotes: "d" for days, "m" for whole months, "y" for whole years. |
How to use DATEDIF
DATEDIF takes the earlier date first, then the later one, then a unit in quotation marks: "d" for days, "m" for whole months, "y" for whole years.
It counts completed units and discards the remainder. Two dates a month and twenty-nine days apart are one month apart to DATEDIF.
That is right for anything measured in periods served: months of a prepayment released, years of service, complete months a debt has been outstanding.
The argument order is the reverse of DAYS, which is worth pausing over every time you write one after the other.
Examples
Every result below is worked out by the same evaluator that marks your answers, on the sheet shown here.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Reporting date | 2026-10-31 | |||
| 2 | |||||
| 3 | Invoice | Invoice date | Terms (days) | Amount | Customer |
| 4 | INV-9001 | 2026-07-14 | 30 | 1200 | Calder Utilities |
| 5 | INV-9002 | 2026-08-29 | 30 | 640 | Brightwell Paper |
| 6 | INV-9003 | 2026-09-03 | 45 | 3100 | Meridian Freight |
| 7 | INV-9004 | 2026-09-30 | 14 | 455 | Thornbury Print |
| 8 | INV-9005 | 2026-10-12 | 30 | 2080 | Calder Utilities |
| 9 | INV-9006 | 2026-10-27 | 60 | 725 | Ashgrove Catering |
| Formula | Result |
|---|---|
=DATEDIF(B4,B1,"d")The same number DAYS gives, with the arguments in the other order. | 109 |
=DATEDIF(B4,B1,"m")Complete months. The leftover days are dropped rather than rounded. | 3 |
=DATEDIF(B4,B1,"y")Nought complete years, because the gap has not reached one. | 0 |
=DATEDIF(B8,B1,"m")An invoice from earlier this month is nought complete months old, which is the answer a period count wants and not the answer an ageing wants. | 0 |
Notes
- DATEDIF is the one Excel function with no argument help in the formula bar. It works, it is just undocumented in the interface.
- It errors if the first date is later than the second, rather than returning a negative.
Related
A reference tells you what it does. A unit makes you use it.