Introduction
The DATEDIF function in Excel calculates the difference between two dates. It can show the result in complete years, complete months, or total days. This function is useful for calculating employee experience, student age, project duration, and the time between two important dates.
What Is the DATEDIF Function in Excel?
The DATEDIF function calculates how much time has passed between a starting date and an ending date.
For example, suppose an employee joined a company on 15-Jan-2020 and you want to calculate their completed years of service as of 15-Jan-2026.
DATEDIF can return 6 years.
You can use this function to calculate:
- Completed years between two dates.
- Completed months between two dates.
- Total days between two dates.
- Remaining months after completed years.
- Remaining days after completed months.
DATEDIF Function Syntax
=DATEDIF(start_date,end_date,unit)
Understanding the Arguments
| Argument | Meaning |
|---|---|
| start_date | The starting date of the period. |
| end_date | The ending date of the period. |
| unit | Specifies whether the result should be in years, months, or days. |
Important: The ending date must be equal to or later than the starting date. Otherwise, Excel returns an error.
Understanding DATEDIF Units
The third argument, called unit, tells Excel what type of date difference you want.
| Unit | Meaning | Result |
|---|---|---|
| “Y” | Complete years | Number of full years |
| “M” | Complete months | Number of full months |
| “D” | Total days | Total number of days |
| “YM” | Remaining months | Months after subtracting complete years |
| “MD” | Remaining days | Days after subtracting complete months |
| “YD” | Remaining days within a year | Days between dates, ignoring the year difference |
For example, consider these dates:
- Starting Date: 15-Jan-2020
- Ending Date: 20-Mar-2026
| Formula | Expected Result | Explanation |
|---|---|---|
=DATEDIF(A2,B2,"Y") | 6 | Six complete years |
=DATEDIF(A2,B2,"M") | 74 | Seventy-four complete months |
=DATEDIF(A2,B2,"D") | 2255 | Total days |
=DATEDIF(A2,B2,"YM") | 2 | Two remaining months |
=DATEDIF(A2,B2,"MD") | 5 | Five remaining days |
=DATEDIF(A2,B2,"YD") | 65 | Days ignoring the year difference |
How does it work?
DATEDIF checks the starting and ending dates and calculates the difference according to the selected unit.
For example:
"Y"counts only complete years."M"counts only complete months."D"counts every day between the dates."YM"ignores complete years and returns the remaining months."MD"ignores complete months and returns the remaining days."YD"ignores the year difference and calculates the remaining days.
Note: The "MD" unit has known limitations for certain date combinations. Use it carefully when accuracy around month-end dates is important.
Practical Examples of DATEDIF Function in Excel
Question 1: Calculate Employee Experience in Years
Problem Statement:
An HR department wants to calculate how many complete years each employee has worked in the company.
Employee Data:
| Employee Name | Joining Date | Calculation Date |
|---|---|---|
| Rahul | 15-Jan-2020 | 15-Jan-2026 |
| Priya | 10-Mar-2021 | 20-Mar-2026 |
| Amit | 05-Jun-2019 | 05-Jun-2026 |
| Neha | 12-Aug-2022 | 10-Aug-2026 |
Excel Formula:
Enter this formula in D2:
=DATEDIF(B2,C2,"Y")
Expected Result:
| Employee Name | Completed Experience |
|---|---|
| Rahul | 6 Years |
| Priya | 5 Years |
| Amit | 7 Years |
| Neha | 3 Years |
Explanation:
The formula checks the joining date in B2 and the calculation date in C2.
The "Y" unit tells Excel to count only complete years. For Neha, the calculation date is two days before her fourth joining anniversary, so Excel returns 3.
Concept Covered: Calculating completed years between two dates.
Question 2: Calculate Employee Service in Complete Months
Problem Statement:
An HR manager wants to calculate the total number of complete months employees have worked in the company.
Employee Data:
| Employee Name | Joining Date | Calculation Date |
|---|---|---|
| Aman | 01-Jan-2025 | 01-Jul-2026 |
| Riya | 15-Feb-2025 | 15-Aug-2026 |
| Karan | 10-Apr-2025 | 09-Apr-2026 |
| Simran | 01-Jun-2025 | 01-Dec-2026 |
Excel Formula:
Enter this formula in D2:
=DATEDIF(B2,C2,"M")
Expected Result:
| Employee Name | Complete Months |
|---|---|
| Aman | 18 |
| Riya | 18 |
| Karan | 11 |
| Simran | 18 |
Explanation:
The "M" unit calculates complete months between two dates.
For Karan, the period from 10-Apr-2025 to 09-Apr-2026 is one day short of 12 complete months. Therefore, Excel returns 11.
Concept Covered: Counting complete months rather than calendar month numbers.
Question 3: Calculate Total Days Between Two Dates
Problem Statement:
A company wants to calculate the total number of days taken to complete different projects.
Project Data:
| Project Name | Start Date | Completion Date |
|---|---|---|
| Website Design | 01-Jan-2026 | 15-Jan-2026 |
| Mobile App | 05-Feb-2026 | 25-Feb-2026 |
| Database Setup | 10-Mar-2026 | 10-Apr-2026 |
| Software Testing | 01-May-2026 | 01-Jun-2026 |
Excel Formula:
Enter this formula in D2:
=DATEDIF(B2,C2,"D")
Expected Result:
| Project Name | Total Days |
|---|---|
| Website Design | 14 |
| Mobile App | 20 |
| Database Setup | 31 |
| Software Testing | 31 |
Explanation:
The "D" unit calculates the total number of elapsed days between the two dates.
For Website Design, the difference between 1 January and 15 January is 14 days.
Concept Covered: Calculating total elapsed days.
Question 4: Calculate Remaining Months After Complete Years
Problem Statement:
An HR department wants to show employee service duration in complete years and the remaining months.
Employee Data:
| Employee Name | Joining Date | Calculation Date |
|---|---|---|
| Rahul | 15-Jan-2020 | 20-Mar-2026 |
| Priya | 01-Feb-2021 | 01-May-2026 |
| Amit | 10-Jun-2022 | 10-Sep-2025 |
Excel Formula:
Enter this formula in D2:
=DATEDIF(B2,C2,"YM")
Expected Result:
| Employee Name | Remaining Months |
|---|---|
| Rahul | 2 |
| Priya | 3 |
| Amit | 3 |
Explanation:
The "YM" unit calculates the remaining complete months after ignoring complete years.
For Rahul, the total duration is 6 complete years and 2 additional complete months. Therefore, the result is 2.
Concept Covered: Separating remaining months from complete years.
Question 5: Calculate Remaining Days After Complete Months
Problem Statement:
A project manager wants to calculate the remaining days after counting complete months between two dates.
Project Data:
| Project Name | Start Date | End Date |
|---|---|---|
| Project A | 01-Jan-2026 | 10-Mar-2026 |
| Project B | 05-Feb-2026 | 15-Apr-2026 |
| Project C | 10-Jun-2026 | 20-Aug-2026 |
Excel Formula:
Enter this formula in D2:
=DATEDIF(B2,C2,"MD")
Expected Result:
| Project Name | Remaining Days |
|---|---|
| Project A | 9 |
| Project B | 10 |
| Project C | 10 |
Explanation:
The "MD" unit returns the remaining days after complete months are ignored.
For Project A, the period from 1 January to 10 March contains two complete months and 9 additional days.
Concept Covered: Calculating the day portion of a date difference. Remember that "MD" has limitations with some month-end date combinations.
Question 6: Calculate Days While Ignoring the Year Difference
Problem Statement:
A company wants to calculate the day difference between two dates while ignoring the difference in years.
Date Data:
| Employee Name | Starting Date | Ending Date |
|---|---|---|
| Rahul | 15-Jan-2020 | 20-Mar-2026 |
| Priya | 10-Feb-2021 | 25-Apr-2026 |
| Amit | 05-Jun-2022 | 15-Aug-2026 |
Excel Formula:
Enter this formula in D2:
=DATEDIF(B2,C2,"YD")
Expected Result:
| Employee Name | Days Ignoring Years |
|---|---|
| Rahul | 65 |
| Priya | 74 |
| Amit | 71 |
Explanation:
The "YD" unit calculates the number of days between the dates while ignoring the year difference.
For Rahul, Excel considers the period from 15 January to 20 March, without counting the six-year difference.
Concept Covered: Calculating a day difference without including complete years.
Question 7: Calculate Student Age in Complete Years
Problem Statement:
A school wants to calculate the completed age of students using their date of birth and a fixed calculation date.
Student Data:
| Student Name | Date of Birth | Calculation Date |
|---|---|---|
| Aarav | 10-May-2010 | 09-Nov-2026 |
| Riya | 15-Aug-2012 | 09-Nov-2026 |
| Kabir | 20-Dec-2011 | 09-Nov-2026 |
| Anaya | 01-Jan-2009 | 09-Nov-2026 |
Excel Formula:
Enter this formula in D2:
=DATEDIF(B2,C2,"Y")
Expected Result:
| Student Name | Completed Age |
|---|---|
| Aarav | 16 Years |
| Riya | 14 Years |
| Kabir | 14 Years |
| Anaya | 17 Years |
Explanation:
The formula calculates the number of complete years between the student’s date of birth and the calculation date.
Kabir has not reached his birthday in December 2026, so Excel returns 14 instead of 15.
Concept Covered: Calculating completed age using two dates.
Student Practice Tasks
Try solving these tasks using only the DATEDIF function.
Task 1: Employee Experience
| Employee | Joining Date | Calculation Date |
|---|---|---|
| Mohit | 01-Apr-2021 | 01-Apr-2026 |
| Sneha | 15-Jul-2022 | 20-Jul-2026 |
| Varun | 10-Oct-2023 | 09-Oct-2026 |
Calculate completed years of experience.
Task 2: Calculate Complete Months
| Customer | Registration Date | Calculation Date |
|---|---|---|
| Customer A | 01-Jan-2026 | 01-May-2026 |
| Customer B | 15-Feb-2026 | 14-Jun-2026 |
| Customer C | 10-Mar-2026 | 10-Sep-2026 |
Calculate complete months for each customer.
Task 3: Calculate Total Days
| Project | Start Date | End Date |
|---|---|---|
| Project X | 01-Feb-2026 | 20-Feb-2026 |
| Project Y | 10-Mar-2026 | 10-Apr-2026 |
| Project Z | 05-Jun-2026 | 25-Jun-2026 |
Calculate the total elapsed days for each project.
Final Student Challenge: Build an Employee Service Duration Report
Problem Statement:
You are working in the HR department. Your manager wants an employee report showing service duration in years, months, and days.
Use the following employee data.
| Employee Name | Joining Date | Calculation Date |
|---|---|---|
| Rahul | 15-Jan-2020 | 20-Mar-2026 |
| Priya | 10-Feb-2021 | 25-Apr-2026 |
| Amit | 05-Jun-2022 | 15-Aug-2026 |
| Neha | 01-Sep-2023 | 10-Nov-2026 |
Your Tasks:
- Calculate complete service years using
"Y". - Calculate complete service months using
"M". - Calculate total service days using
"D". - Calculate remaining months using
"YM". - Calculate remaining days using
"MD".
Expected Report Structure:
| Employee Name | Service Years | Service Months | Total Days | Remaining Months | Remaining Days |
|---|---|---|---|---|---|
| Rahul | Your Result | Your Result | Your Result | Your Result | Your Result |
| Priya | Your Result | Your Result | Your Result | Your Result | Your Result |
| Amit | Your Result | Your Result | Your Result | Your Result | Your Result |
| Neha | Your Result | Your Result | Your Result | Your Result | Your Result |
Complete the report by applying the correct DATEDIF unit in each column.
Key Takeaways
- DATEDIF calculates the difference between two dates.
"Y"returns complete years."M"returns complete months."D"returns total elapsed days."YM"returns remaining months after complete years."MD"returns remaining days after complete months, but has known limitations for some date combinations."YD"calculates days while ignoring the year difference.- The ending date must not be earlier than the starting date.
- DATEDIF is useful for calculating employee experience, age, and project duration.
Frequently Asked Questions (FAQs)
1. What is the DATEDIF function in Excel?
DATEDIF calculates the difference between two dates in complete years, months, or days, depending on the selected unit.
2. What is the syntax of the DATEDIF function?
The syntax is:
=DATEDIF(start_date,end_date,unit)
3. How do I calculate age using DATEDIF?
Use the "Y" unit to calculate completed years between a date of birth and a calculation date.
=DATEDIF(A2,B2,"Y")
4. How do I calculate the total number of days between two dates?
Use the "D" unit.
=DATEDIF(A2,B2,"D")
5. What is the difference between “Y” and “M” in DATEDIF?
"Y" counts complete years, while "M" counts complete months.
6. What does “YM” mean in DATEDIF?
"YM" calculates the remaining complete months after ignoring complete years.
7. What does “MD” mean in DATEDIF?
"MD" calculates the remaining days after ignoring complete months. However, this unit has known limitations with some date combinations.
8. What does “YD” mean in DATEDIF?
"YD" calculates the number of days between two dates while ignoring the difference in years.
9. Why is DATEDIF not appearing in Excel’s function suggestions?
DATEDIF is a compatibility function that may not appear in Excel’s formula autocomplete list. You can still type the formula manually.
10. Can DATEDIF calculate employee experience?
Yes. You can use the "Y" unit to calculate completed years of service, "M" for complete months, and "D" for total elapsed days.

