Date
Measuring a span between dates
Definition
Measuring a span between two dates means giving DATEDIF the start date, the end date, and a unit code.
Syntax
=DATEDIF(start_date, end_date, unit)
Arguments
| Argument | Meaning |
|---|---|
start_date |
The earlier date, the start of the span. |
end_date |
The later date, the end of the span. |
unit |
The unit in double quotes. "Y" counts complete years; "M" counts complete months. |
Worked example
A lease list stores the start in B2 and the end in C2. The span is counted in complete months.
| A: Unit | B: Start | C: End |
|---|---|---|
| 12A | 1 Apr 2019 | 1 Apr 2021 |
| 12B | 1 May 2020 | 1 Nov 2020 |
| 12C | 1 Jan 2018 | 1 Jan 2019 |
Formula: =DATEDIF(B2,C2,"M")
Result: 24. From 1 April 2019 to 1 April 2021 is 24 complete months.
Practice
Cell A2 holds the date 10 March 2018. Cell B2 holds the date 10 March 2022. 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 10 March 2018 to 10 March 2022 is 4 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 4.
The next lesson is Counting days between two dates.
Related functions
DATEDIF is the function that measures that span in the unit you name.
Common mistake
Leaving the quotes off the unit, as in =DATEDIF(B2,C2,M): Excel looks for a name called M instead of counting months.