**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.