Excel DATEDIF Function
DATEDIF Function
The DATEDIF function is a premade function in Excel, which calculates the difference between two dates in years, months or days.
It is typed =DATEDIF and has the following parts:
=DATEDIF(start_date, end_date, unit)
Note: DATEDIF is not shown in the list of suggestions when you start typing a function, so you have to type the whole name yourself. It is kept for compatibility with older spreadsheet programs, like Lotus 1-2-3, but it works in all current versions of Excel.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Start_date: The first date. It must be earlier than, or the same as, the end date.
End_date: The last date. Use TODAY() to count up to today.
Unit: What to count, as text in double quotes. The examples are for 2019-04-01 to 2026-09-28:
| Unit | Returns | Example |
|---|---|---|
"Y" | Complete years | 7 |
"M" | Complete months | 89 |
"D" | Days | 2737 |
"MD" | Days, ignoring months and years | 27 |
"YM" | Months, ignoring years | 5 |
"YD" | Days, ignoring years | 180 |
Warning: Microsoft recommends that you do not use "MD". In some cases it gives a negative number, zero, or a wrong result.
How to use the DATEDIF function:
- Select a cell (
C2) - Type
=DATEDIF( - Select the start date (
B2) - Type (
,) - Type or select the end date (
DATE(2026,9,28)) - Type (
,) - Type the unit in double quotes (
"Y") - Hit enter
Let's have a look at an example!
The Poke Mart has a club for trainers. This table shows when some trainers became members (example data). Copy it and paste it into cell A1:
Example data
A B 1 Trainer Member Since 2 Ash 2019-04-01 3 Misty 2021-11-30 4 Brock 2016-09-28 5 Dawn 2024-06-15 6 Gary 2026-02-10
Note: Excel may show the dates in another format, like 4/1/2019, depending on your settings. The results are the same.
Type Years, Months and Days in C1, D1 and E1. Find how long each trainer has been a member, up to 2026-09-28. Type the formulas in C2, D2 and E2, and fill them down to row 6:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Trainer | Member Since | Years | Months | Days |
| 2 | Ash | 2019-04-01 | 7 | 89 | 2737 |
| 3 | Misty | 2021-11-30 | 4 | 57 | 1763 |
| 4 | Brock | 2016-09-28 | 10 | 120 | 3652 |
| 5 | Dawn | 2024-06-15 | 2 | 27 | 835 |
| 6 | Gary | 2026-02-10 | 0 | 7 | 230 |
The formula in C2:
=DATEDIF(B2, DATE(2026,9,28), "Y")
The formula in D2:
=DATEDIF(B2, DATE(2026,9,28), "M")
The formula in E2:
=DATEDIF(B2, DATE(2026,9,28), "D")
Ash became a member on 2019-04-01. That is 7 complete years, 89 complete months, or 2737 days.
Brock became a member exactly 10 years ago. Misty became a member on 2021-11-30. Her 5th year is not complete until 2026-11-30, so her Years result is 4.
The DATE function makes a date from a year, a month and a day. In your own sheet, use TODAY() instead of DATE(2026,9,28) to count up to the current date. The results then change every day. See TODAY.
Years and Months
To show a length of time like 7 years, 5 months, combine "Y" with "YM". "YM" counts the months that are left after the complete years.
Type Membership in F1. Type this formula in F2 and fill it down to F6:
| A | B | F | |
|---|---|---|---|
| 1 | Trainer | Member Since | Membership |
| 2 | Ash | 2019-04-01 | 7 years, 5 months |
| 3 | Misty | 2021-11-30 | 4 years, 9 months |
| 4 | Brock | 2016-09-28 | 10 years, 0 months |
| 5 | Dawn | 2024-06-15 | 2 years, 3 months |
| 6 | Gary | 2026-02-10 | 0 years, 7 months |
=DATEDIF(B2, DATE(2026,9,28), "Y") & " years, " & DATEDIF(B2, DATE(2026,9,28), "YM") & " months"
The & symbol joins the numbers and the words together. You can also do this with CONCAT.
Ash has been a member for 89 months. That is 7 complete years (84 months) and 5 more months.
Note: Text in a formula must be inside double quotes: " ". Notice the spaces inside the quotes, like " years, ". Without them, the words would stick to the numbers.
Start Date After End Date
The start date must come first. If the start date is later than the end date, DATEDIF returns #NUM!.
This formula in G2 has the two dates in the wrong order:
| A | B | G | |
|---|---|---|---|
| 1 | Trainer | Member Since | Wrong Order |
| 2 | Ash | 2019-04-01 | #NUM! |
=DATEDIF(DATE(2026,9,28), B2, "Y")
Swap the two dates to fix it: =DATEDIF(B2, DATE(2026,9,28), "Y")
See Also
To count the days between two dates, you can also subtract them. =DATE(2026,9,28)-B2 gives the same result as the "D" unit.
NETWORKDAYS counts only the working days between two dates, and TODAY returns the current date.
