The DATEDIF function
Counts the whole years, months or days between two dates, which is how you work out an age or a length of service. Excel leaves it out of the function list, but it works.
Syntax
DATEDIF(start_date, end_date, unit)- start_date
- The first date, such as a date of birth or a hire date.
- end_date
- The last date. It must not be before start_date, or the result is
- unit
- "Y" for whole years, "M" for whole months, "D" for days, "YM" for the months left over after whole years, "MD" for the days left over after whole months.
Examples
B1
=DATEDIF(1DATE(1990,6,15),2DATE(2024,3,15),3"Y")| A | B | |
|---|---|---|
| 1 | 33 |
In plain words: Count the 3whole years from 1Jun 15, 1990 to 2Mar 15, 2024.
B1=DATEDIF(DATE(2024,1,31),DATE(2024,3,15),"M")
| A | B | |
|---|---|---|
| 1 | 1 |
B1=DATEDIF(DATE(2020,9,1),DATE(2024,3,15),"Y")&" years, "&DATEDIF(DATE(2020,9,1),DATE(2024,3,15),"YM")&" months"
| A | B | |
|---|---|---|
| 1 | 3 years, 6 months |
Practice DATEDIF
Questions
How do I calculate someone's age in Excel?
Use =DATEDIF(B2,TODAY(),"Y") with the date of birth in B2. It counts whole years, so the age goes up on the birthday itself.
Why can't I find DATEDIF in the list of functions?
Excel keeps DATEDIF for compatibility with old Lotus 1-2-3 files and does not suggest it as you type. Type the whole name and it works in every version.
Why does DATEDIF return
The start date is after the end date. Swap the two arguments.