Part 1: Introduce
Definition
The DATEDIF function in Excel calculates the number of days, months, or years between two dates. It’s provided to support older workbooks from Lotus 1-2-3 but may calculate incorrect results under specific scenarios.
Purpose
The function is helpful in formulas where you must calculate age or the difference between two dates in various units like days, months, or years.
Syntax & Arguments
DATEDIF(start_date, end_date, unit) - start_date: A date representing a given period’s first or starting date.
- end_date: A date that represents the period’s last, or ending, date.
- Unit: The type of information that you want to be returned. It can be:
- “Y”: The number of complete years in the period.
- “M”: The number of whole months in the period.
- “D”: The number of days in the period.
- “MD”: The difference between the days in start_date and end_date (months and years ignored).
- “YM”: The difference between the months in start_date and end_date (days and years forgotten).
- “YD”: The difference between the days of start_date and end_date (years forgotten).
Return Value
The function returns the difference between the two dates in the specified unit.
Remarks
- Dates are stored as sequential serial numbers to be used in calculations.
- If the start_date exceeds the end_date, the result will be #NUM!
- The “MD” argument may result in a negative number, zero, or an inaccurate result.
Part 2: Examples
Example 1: Calculating Years Between Two Dates
Purpose of Example: Calculate the number of complete years between two dates.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result |
| 2 | 1/1/2001 | 1/1/2003 | =DATEDIF(A2, B2, "Y") | 2 |
Explanation: This example calculates two complete years between the start and end dates.
Example 2: Calculating Days Between Two Dates
Purpose of Example: Calculate the number of days between two dates.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result |
| 2 | 6/1/2001 | 8/15/2002 | =DATEDIF(A2, B2, "D") | 440 |
Explanation: This example calculates 440 days between the start and end dates.
Example 3: Calculating Months Between Two Dates
Purpose of Example: Calculate the number of complete months between two dates.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result |
| 2 | 3/15/2019 | 6/15/2020 | =DATEDIF(A2, B2, "M") | 15 |
Explanation: This example calculates 15 complete months between the start and end dates.
Example 4: Calculating the Difference in Days, Ignoring Years
Purpose of Example: Calculate the difference in days between two dates, ignoring the years.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result |
| 2 | 6/1/2001 | 8/15/2002 | =DATEDIF(A2, B2, "YD") | 75 |
Explanation: This example calculates 75 days between June 1 and August 15, ignoring the years of the dates.
Example 5: Calculating the Difference in Months, Ignoring Days and Years
Purpose of Example: Calculate the difference in months between two dates, ignoring the days and years.
Data Tables and Formulas
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result |
| 2 | 3/15/2019 | 6/15/2020 | =DATEDIF(A2, B2, "YM") | 3 |
Explanation: This example calculates the difference of 3 months between the start and end dates, ignoring the days and years.
Example 6: Using IF with DATEDIF
Purpose of Example: Determine if the difference between the two dates is more than a year.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result | Evaluation |
| 2 | 1/1/2020 | 1/1/2021 | =DATEDIF(A2, B2, "Y") | 1 | =IF(D2>1, "Yes", "No") |
Explanation: This example calculates the difference in years between the start and end dates and then uses the IF function to determine if the difference is more than a year. The result is “No.”
Example 7: Using SUM with DATEDIF
Purpose of Example: Calculate the number of days between multiple date ranges.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result | Total Days |
| 2 | 1/1/2020 | 1/15/2020 | =DATEDIF(A2, B2, "D") | 14 | =SUM(D2:D4) |
| 3 | 2/1/2020 | 2/15/2020 | =DATEDIF(A3, B3, "D") | 14 | |
| 4 | 3/1/2020 | 3/15/2020 | =DATEDIF(A4, B4, "D") | 14 |
Explanation: This example calculates the number of days between several date ranges and then uses the SUM function to calculate the total number of days. The total days are 42.
Example 8: Using VLOOKUP with DATEDIF
Purpose of Example: Find the difference in days between two dates based on a lookup value.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Start Date | End Date | Formula | Result |
| 2 | 101 | 1/1/2020 | 1/15/2020 | =DATEDIF(B2, C2, "D") | 14 |
| 3 | 102 | 2/1/2020 | 2/15/2020 | =DATEDIF(B3, C3, "D") | 14 |
| G | H | I | J | |
|---|---|---|---|---|
| 1 | Lookup ID | Formula | Result | |
| 2 | 101 | =VLOOKUP(G2, A2:E3, 5, FALSE) | 14 |
Explanation: This example uses the DATEDIF function to calculate the difference in days between two dates for different IDs. Then, it uses the VLOOKUP function to find the difference in days for a specific ID. The result is 14.
Example 9: Using AVERAGE with DATEDIF
Purpose of Example: Calculate the average months between multiple date ranges.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result | Average Months |
| 2 | 1/1/2020 | 4/1/2020 | =DATEDIF(A2, B2, "M") | 3 | =AVERAGE(D2:D4) |
| 3 | 2/1/2020 | 5/1/2020 | =DATEDIF(A3, B3, "M") | 3 | |
| 4 | 3/1/2020 | 6/1/2020 | =DATEDIF(A4, B4, "M") | 3 |
Explanation: This example calculates the number of months between several date ranges and then uses the AVERAGE function to calculate the average number of months. The middle months are 3.
Example 10: Using CONCATENATE with DATEDIF
Purpose of Example: Create a text string that describes the difference in years between two dates.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result | Description |
| 2 | 1/1/2000 | 1/1/2020 | =DATEDIF(A2, B2, "Y") | 20 | =CONCATENATE("The difference is ", D2, " years.") |
Explanation: This example calculates the difference in years between two dates and then uses the CONCATENATE function to create a text string that describes the difference. The description is “The difference is 20 years.”
Example 11: Using COUNTIF with DATEDIF
Purpose of Example: Count how many date ranges have a difference more significant than 10 days.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result | Count |
| 2 | 1/1/2020 | 1/11/2020 | =DATEDIF(A2, B2, "D") | 10 | =COUNTIF(D2:D4, ">10") |
| 3 | 2/1/2020 | 2/12/2020 | =DATEDIF(A3, B3, "D") | 11 | |
| 4 | 3/1/2020 | 3/9/2020 | =DATEDIF(A4, B4, "D") | 9 |
Explanation: This example calculates the number of days between several date ranges and then uses the COUNTIF function to count how many date ranges have a difference more significant than 10 days. The count is 1.
Example 12: Using MIN with DATEDIF
Purpose of Example: Find the minimum difference in months between multiple date ranges.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result | Min Months |
| 2 | 1/1/2020 | 3/1/2020 | =DATEDIF(A2, B2, "M") | 2 | =MIN(D2:D4) |
| 3 | 2/1/2020 | 4/1/2020 | =DATEDIF(A3, B3, "M") | 2 | |
| 4 | 3/1/2020 | 5/1/2020 | =DATEDIF(A4, B4, "M") | 2 |
Explanation: This example calculates the months between several date ranges and then uses the MIN function to find the minimum difference in months. The minimum months are 2.
Example 13: Using MAX with DATEDIF
Purpose of Example: Find the maximum-day difference between multiple date ranges.
Data Tables and Formulas
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Start Date | End Date | Formula | Result | Max Days |
| 2 | 1/1/2020 | 1/15/2020 | =DATEDIF(A2, B2, "D") | 14 | =MAX(D2:D4) |
| 3 | 2/1/2020 | 2/10/2020 | =DATEDIF(A3, B3, "D") | 9 | |
| 4 | 3/1/2020 | 3/20/2020 | =DATEDIF(A4, B4, "D") | 19 |
Explanation: This example calculates the number of days between several date ranges and then uses the MAX function to find the maximum difference in days. The maximum number of days is 19.
Part 3: Tips and Tricks
- Be cautious when using the “MD” argument, as it may result in incorrect results.
- The DATEDIF function is handy for calculating ages or time spans in different units.
- Ensure that the start_date is less than or equal to the end_date to avoid errors.
By following these examples and understanding the syntax, you can utilize the DATEDIF function in various business scenarios to calculate differences between dates in Excel.