# DATEDIFF Function in Excel

### 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

syntax
```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

ABCD
1Start DateEnd DateFormulaResult
21/1/20011/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

ABCD
1Start DateEnd DateFormulaResult
26/1/20018/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

ABCD
1Start DateEnd DateFormulaResult
23/15/20196/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

ABCD
1Start DateEnd DateFormulaResult
26/1/20018/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

ABCD
1Start DateEnd DateFormulaResult
23/15/20196/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

ABCDE
1Start DateEnd DateFormulaResultEvaluation
21/1/20201/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

ABCDE
1Start DateEnd DateFormulaResultTotal Days
21/1/20201/15/2020`=DATEDIF(A2, B2, "D")`14`=SUM(D2:D4)`
32/1/20202/15/2020`=DATEDIF(A3, B3, "D")`14
43/1/20203/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

ABCDE
1IDStart DateEnd DateFormulaResult
21011/1/20201/15/2020`=DATEDIF(B2, C2, "D")`14
31022/1/20202/15/2020`=DATEDIF(B3, C3, "D")`14
GHIJ
1Lookup IDFormulaResult
2101`=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

ABCDE
1Start DateEnd DateFormulaResultAverage Months
21/1/20204/1/2020`=DATEDIF(A2, B2, "M")`3`=AVERAGE(D2:D4)`
32/1/20205/1/2020`=DATEDIF(A3, B3, "M")`3
43/1/20206/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

ABCDE
1Start DateEnd DateFormulaResultDescription
21/1/20001/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

ABCDE
1Start DateEnd DateFormulaResultCount
21/1/20201/11/2020`=DATEDIF(A2, B2, "D")`10`=COUNTIF(D2:D4, ">10")`
32/1/20202/12/2020`=DATEDIF(A3, B3, "D")`11
43/1/20203/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

ABCDE
1Start DateEnd DateFormulaResultMin Months
21/1/20203/1/2020`=DATEDIF(A2, B2, "M")`2`=MIN(D2:D4)`
32/1/20204/1/2020`=DATEDIF(A3, B3, "M")`2
43/1/20205/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

ABCDE
1Start DateEnd DateFormulaResultMax Days
21/1/20201/15/2020`=DATEDIF(A2, B2, "D")`14`=MAX(D2:D4)`
32/1/20202/10/2020`=DATEDIF(A3, B3, "D")`9
43/1/20203/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.