DATEDIF in Excel: how to use it
DATEDIF works out the difference between two dates in years, months or days, such as =DATEDIF(A2,B2,"Y") for an age.
Try the first exercises for freeIn the lesson DATEDIF
What does DATEDIF do?
DATEDIF calculates how many whole years, months or days there are between two dates. With "Y" you get whole years, with "M" whole months and with "D" days.
Excel doesn't show this function in the list of suggestions while you type, but it does work: type it out in full. The start date has to be before the end date. For just the number of days, you can also simply subtract, or use DAYS.
When do you use DATEDIF?
- Someone's age today: =DATEDIF(A2,TODAY(),"Y").
- How many years someone has worked for a company: =DATEDIF(B2,C2,"Y").
- How many full months a contract ran: =DATEDIF(A2,B2,"M").
- A length of time such as "3 years and 5 months": =DATEDIF(A2,B2,"Y")&" years and "&DATEDIF(A2,B2,"YM")&" months".
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The difference in whole years, months or days.
Filled in: =DATEDIF(A2,B2,"Y")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
DATEDIF | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | DATEDIF |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
start_date | The earlier date. | A2 |
end_date | The later date. | B2 |
unit | "Y" for whole years, "M" for whole months, "D" for days and "YM" for the months that are left after the full years. | "Y" |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. |
DATEDIF step by step
C2 contains =DATEDIF(A2,B2,"Y"). From 12 September 2015 to 1 September 2026, there are 10 whole years. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C2 and type DATEDIF with a bracket. Excel doesn't suggest the function, but it does work.
=DATEDIF( - Click the earlier date, A2, type a comma and click the later date, B2.
=DATEDIF(A2,B2 - Type a comma and the unit in quotation marks: "Y" for whole years.
=DATEDIF(A2,B2,"Y" - Close the bracket and press Enter: 10 whole years. The eleventh year isn't complete yet.
=DATEDIF(A2,B2,"Y")
More examples
Whole months
C2 contains =DATEDIF(A2,B2,"M"). Up to 12 August 2026, there are 131 full months, and the next month isn't complete yet.
Years and months
C2 contains =DATEDIF(A2,B2,"Y")&" years and "&DATEDIF(A2,B2,"YM")&" months". "YM" gives the months that are left after the full years.
Days with DAYS
C2 contains =DAYS(B2,A2). DAYS takes the end date first: 45 days. =B2-A2 gives the same.
A hidden function
Excel doesn't show this function in its suggestions, but it does work. So type it out in full.
Where it often goes wrong
- The start date must come before the end date, otherwise you get #NUM!.
Practise this in the lesson DATEDIF. The first exercises are free.
Frequently asked questions
How do I work out an age?
=DATEDIF(date_of_birth,TODAY(),"Y").
Read next
DATEYEAR, MONTH and DAYTODAYNETWORKDAYSCalculating with time