Complete years
=DATEDIF(B2,C2,"Y")Returns complete years between the dates.
Calculate complete years, months, or days between two dates with DATEDIF, including unit choices, #NUM! behavior, and MD limitations.
Direct answer
Use =DATEDIF(B2,C2,"Y") to return the number of complete years between the start date in B2 and end date in C2.
=DATEDIF(B2,C2,"Y")Returns complete years between the dates.
=DATEDIF(B2,C2,"M")Returns complete months between the dates.
=DATEDIF(B2,C2,"D")Returns the number of days between the dates.
=DATEDIF(B2,C2,"YM")Returns remaining months after complete years.
=DATEDIF(B2,C2,"YD")Returns the day difference while ignoring years.
Press ⌘+Enter to submit
Need more than a single DATEDIF Excel Formula formula? Try our free AI for Excel — it reads your entire spreadsheet, answers questions in plain English, and builds charts and analysis in the same chat.
DATEDIF returns complete years of service between each hire date and the review date.
| Employee | Hire date (B) | Review date (C) | Complete years |
|---|---|---|---|
| Amina | 3/15/2019 | 8/3/2026 | 7 |
| Ben | 11/20/2021 | 8/3/2026 | 4 |
| Cara | 7/30/2024 | 8/3/2026 | 2 |
Put the earlier start date in B2 and the later end date in C2.
Use Y for complete years, M for complete months, D for days, YM for remaining months, or YD for days ignoring years.
Type =DATEDIF(B2,C2,"Y") and press Enter.
If the start date is later than the end date, DATEDIF returns #NUM!.
| Unit | Returns |
|---|---|
| Y | Complete years |
| M | Complete months |
| D | Total days |
| YM | Months after complete years |
| YD | Days while ignoring years |
start_date:
The starting date from which you want to calculate the difference. This can be a date entered directly, a cell reference containing a date, or a formula that returns a date.
end_date:
The ending date up to which you want to calculate the difference. This can also be a date entered directly, a cell reference containing a date, or a formula that returns a date.
unit:
The unit of time that you want to use for the difference calculation. This can be "Y" for years, "M" for months, "D" for days, "MD" for the difference in days, ignoring months and years, "YM" for the difference in months, ignoring years, or "YD" for the difference in days, ignoring years.
The DATEDIF function in Excel is a useful tool that allows you to calculate the difference between two dates in various units such as days, months, or years. It helps you determine the time span between two dates, making it easier to analyze time-related data.
In simpler terms, the DATEDIF function helps you find out how much time has passed between two dates, which can be particularly useful for project management, age calculations, and other time-sensitive analyses.
Imagine you are tracking the duration of employee service in a company, and you have the following data in an Excel spreadsheet:
| Employee | Start Date | End Date |
|---|---|---|
| Alice | 01/01/2015 | 01/01/2023 |
| Bob | 05/15/2018 | 05/15/2023 |
| Charlie | 03/10/2020 | 03/10/2023 |
| David | 07/20/2017 | 07/20/2023 |
| Eve | 12/01/2019 | 12/01/2023 |
You want to find out how many years each employee has been with the company.
To calculate the years of service for each employee, you would use the DATEDIF formula as follows:
excel=DATEDIF(B2, C2, "Y")
When you apply the formula for each employee, it calculates the years of service as follows:
| Employee | Years of Service |
|---|---|
| Alice | 8 |
| Bob | 5 |
| Charlie | 3 |
| David | 6 |
| Eve | 4 |
Here, the years of service are calculated based on the difference between the start and end dates.
The DATEDIF function is particularly useful when you need to calculate the time difference between two dates without manually counting days, months, or years. It allows you to quickly derive insights related to time intervals, making it easier to manage projects, track employee tenure, or analyze age-related data.
Understanding how to use the DATEDIF function can significantly enhance your data analysis capabilities in Excel, enabling you to extract meaningful insights from time-related datasets.
Happy calculating!
You have a table where Column A contains employee names and Column B contains their corresponding hire dates. You can use the DATEDIF function to calculate the length of service for each employee in years. This allows for efficient tracking of employee tenure, which can be useful for making decisions regarding promotions, benefits eligibility, and retirement planning without the need for complex calculations.
Beyond the DATEDIF Excel Formula function: Formula Bot is a full Excel AI that goes well past individual formulas. Upload any spreadsheet and ask questions in plain English — you'll get formulas, data analysis, and charts back in seconds. It's a free AI Excel assistant that works with any .xlsx, .xls, or .csv file — no plugins, no Microsoft 365 required.
Don't have a spreadsheet yet? Use our free AI spreadsheet generator to turn a text prompt into a downloadable Excel file — a full AI Excel sheet maker that builds headers, columns, and formulas from your description.
Compare your options
Open a neutral research prompt in the AI tool you already use. Compare capabilities, limitations, and fit before you choose.
AI answers can be incomplete. Check source links, current pricing, and product documentation before deciding.
Sign up for Formula Bot and get unlimited AI-powered formula generation, explanations, and advanced data analysis.