The `DATEDIF` function in Excel is used to calculate the difference between two dates in various units, such as years, months, or days. Here’s how you can use it:
Syntax
The syntax of the `DATEDIF` function is:
=DATEDIF(start_date, end_date, unit)
- start_date: The start date in a valid Excel date format.
- end_date: The end date in a valid Excel date format. The `end_date` must be later than the `start_date`.
- unit: A text string that defines the unit for the result. Below are the available units:
- `”Y”`: Returns the number of complete years between the start and end dates.
- `”M”`: Returns the number of complete months between the start and end dates.
- `”D”`: Returns the number of days between the start and end dates.
- `”MD”`: Returns the difference in days between the start and end dates, ignoring months and years.
- `”YM”`: Returns the difference in months between the start and end dates, ignoring days and years.
- `”YD”`: Returns the difference in days between the start and end dates, ignoring years.
Examples
=DATEDIF(A1, B1, "Y")
=DATEDIF(A1, B1, "M")
=DATEDIF(A1, B1, "D")
=DATEDIF(A1, B1, "MD")
=DATEDIF(A1, B1, "YM")
=DATEDIF(A1, B1, "YD")
- Calculate the number of complete years:
- If `A1` is `01/01/2000` and `B1` is `01/01/2023`, the formula returns `23`.
- Calculate the number of complete months:
- If `A1` is `01/01/2022` and `B1` is `03/01/2023`, the formula returns `14`.
- Calculate the total number of days:
- If `A1` is `01/01/2023` and `B1` is `03/01/2023`, the formula returns `59`.
- Calculate the number of days ignoring years and months:
- If `A1` is `12/25/2022` and `B1` is `01/05/2023`, the formula returns `11`.
- Calculate the number of months ignoring days and years:
- If `A1` is `01/15/2022` and `B1` is `03/20/2023`, the formula returns `2`.
- Calculate the number of days ignoring years:
- If `A1` is `06/15/2022` and `B1` is `07/20/2023`, the formula returns `35`.
Important Notes
- The `DATEDIF` function is not listed in Excel’s Formula Tab, though it can still be used.
- Ensure that the `start_date` is less than the `end_date` to avoid errors.
- The dates should be in a valid Excel date format for the function to work correctly.
By following these guidelines and examples, you can effectively calculate date differences in Excel using the `DATEDIF` function.
Unlock Your Potential
Excel
Access
Power BI
