Excel track · Exercise 72 of 86 Paid
DATEDIF: days, months or years between
Ask for the gap between two dates in whole years, months or days, with the leftovers when needed.
Advanced
Two employees, their start dates and one review date
Read-only data · screen 1 of 2 · answer in D2
The dates in B and C are serials, not text: put the complete years Ada has served into D2, counting only full years.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Started | Review date | Years served |
| 2 | Ada Lovelace | 44562 | 45352 | |
| 3 | Grace Hopper | 44197 | 45352 |
Same person, different question: put the complete months since Grace started into D2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Started | Review date | Full months |
| 2 | Grace Hopper | 44197 | 45352 | |
| 3 | Ada Lovelace | 44562 | 45352 |
Reference answer
What it does
Subtracting one date from another answers in days and nothing else. DATEDIF takes a start date, an end date and a unit in quotes, and answers in whichever unit you name. Y, M and D give whole units; YM, MD and YD give what is left after the whole ones.
Ada started on 1 January 2022 and the review is 1 March 2024, so the Y answer is 2. A subtraction on that pair gives 790 and M gives 26, both correct answers to other questions. The second screen asks for Grace's complete months and expects 38.
Nothing in the call checks that the unit matches the question, so a wrong one returns the answer to a different question. Excel leaves DATEDIF out of its function list, so type the name in full. A start date after the end returns #NUM! instead of a negative count.
Syntax
=DATEDIF(start, end, "unit")Worked example
On Two employees, their start dates and one review date, =DATEDIF(B2,C2,"Y") returns 2.
=DATEDIF(B2,C2,"Y")Common errors
| Error | What it means and how to fix it |
|---|---|
#NUM! | The end date falls before the start date, which leaves DATEDIF no positive interval to report. |
#NUM! | The unit in quotes is not Y, M, D, YM, MD or YD, which DATEDIF cannot count in. |
#VALUE! | One of the dates is text that Excel cannot read as a date, so DATEDIF has no serial to compare. |
At work
A staff review sheet reporting complete years of service, without dividing a day count by 365.