Date
DATEDIF
Definition
DATEDIF returns the number of complete years, months, or days between two dates.
Syntax
=DATEDIF(start_date, end_date, unit)
Arguments
| Argument | Meaning |
|---|---|
start_date |
The earlier date, or the cell holding it. |
end_date |
The later date, or the cell holding it. |
unit |
The span to count, in double quotes: "Y" for complete years, "M" for complete months, "D" for days. |
Worked example
A rental sheet needs complete months between a start and an end. Starts are in A2:A4 and ends are in B2:B4.
| A: Start | B: End |
|---|---|
| 1 Jan 2020 | 1 Apr 2020 |
| 1 Jan 2020 | 1 Jan 2023 |
| 8 Mar 2023 | 8 Jun 2023 |
Formula: =DATEDIF(A2,B2,"M")
Result: 3. From 1 January 2020 to 1 April 2020 there are three complete months.
Practice
Cell A2 holds 1 January 2020 and cell B2 holds 1 January 2023. In C2, write a formula that returns the number of complete years between those dates.
- In C2, write a DATEDIF formula that returns the years from A2 to B2.
- C2 should show the years from A2 to B2.
Step 1 of 2
Enter =DATEDIF(A2,B2,"Y") in C2.
From 1 January 2020 to 1 January 2023 is 3 years.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses DATEDIF.
C2 now shows 3.
Related functions
TODAY can supply an end date when you want the complete years from a start date through today.
Common mistake
Putting the later date first: DATEDIF needs the start date, then the end date, or it returns #NUM!.