DATEDIF — Measuring Time Between Dates
Nisha wants to thank loyalty members on their anniversaries and give a free pastry to anyone who has been a member for at least a year. The loyalty sheet only stores the date each person joined. To turn "joined on 20 August 2023" into "member for 1 year" you need to measure the gap between two dates, and DATEDIF does exactly that.
The loyalty sheet, with the report date in E1:
| A | B | |
|---|---|---|
| 1 | member | joined |
| 2 | Asha Rao | 2023-08-20 |
| 3 | Vikram Shah | 2024-03-15 |
| 4 | Meera Iyer | 2025-01-31 |
| 5 | Dev Menon | 2022-06-02 |
E1 holds 2025-06-02.
The rule
DATEDIF(earlier, later, unit) counts only the complete units between them.
=DATEDIF(B2, $E$1, "Y")- earlier: the start date. It must not be after the end date, or you get
#NUM!. - later: the end date.
$E$1stays fixed when you copy the formula down;TODAY()works too. - unit, in quotes:
"Y"complete years,"M"complete months,"D"days, and"YM"months left over after the whole years are removed.
"Complete" is the key word. DATEDIF never rounds up. Asha joined on 20 August 2023, so on 2 June 2025 she has 1 complete year: her second anniversary is still in the future.
DATEDIF is an older function that Excel keeps for compatibility, so it does not appear in the formula autocomplete list. Type it in full and it works. For plain day counts, =E1-B2 gives the same result as the "D" unit, because Excel stores dates as numbers of days.
A related unit, "MD", can give odd results around month ends. Avoid it in reports.
The shortcut
To check a DATEDIF answer in your head, count anniversaries forward from the start date and stop at the last one that has already arrived.
Years
2024-08-20 has passed (1). 2025-08-20 has not.
"Y"gives 1.
The trap is swapping the arguments. =DATEDIF($E$1, B2, "Y") puts the later date first and returns #NUM!.
Drills
Answer fast — trust the rule.
What does =DATEDIF(B3, $E$1, "Y") return for Vikram (joined 2024-03-15)?
What does =DATEDIF(B3, $E$1, "M") return for Vikram?
Dev joined on 2022-06-02. What does =DATEDIF(B5, $E$1, "Y") return on 2025-06-02?
What does =DATEDIF($E$1, B4, "M") return?
Which formula flags members of at least one complete year with "Gift"?
Remember
- DATEDIF(earlier, later, unit) counts only the complete units between them.
- Shortcut: count anniversaries forward and stop at the last one that has arrived.
- Trap: the later date first gives
#NUM!.