DATEDIF Function in Excel Tutorial

Excel Tutorial

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

ArgumentMeaning
start_dateThe starting date of the period.
end_dateThe ending date of the period.
unitSpecifies 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.

UnitMeaningResult
“Y”Complete yearsNumber of full years
“M”Complete monthsNumber of full months
“D”Total daysTotal number of days
“YM”Remaining monthsMonths after subtracting complete years
“MD”Remaining daysDays after subtracting complete months
“YD”Remaining days within a yearDays between dates, ignoring the year difference

For example, consider these dates:

  • Starting Date: 15-Jan-2020
  • Ending Date: 20-Mar-2026
FormulaExpected ResultExplanation
=DATEDIF(A2,B2,"Y")6Six complete years
=DATEDIF(A2,B2,"M")74Seventy-four complete months
=DATEDIF(A2,B2,"D")2255Total days
=DATEDIF(A2,B2,"YM")2Two remaining months
=DATEDIF(A2,B2,"MD")5Five remaining days
=DATEDIF(A2,B2,"YD")65Days 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 NameJoining DateCalculation Date
Rahul15-Jan-202015-Jan-2026
Priya10-Mar-202120-Mar-2026
Amit05-Jun-201905-Jun-2026
Neha12-Aug-202210-Aug-2026

Excel Formula:

Enter this formula in D2:

=DATEDIF(B2,C2,"Y")

Expected Result:

Employee NameCompleted Experience
Rahul6 Years
Priya5 Years
Amit7 Years
Neha3 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 NameJoining DateCalculation Date
Aman01-Jan-202501-Jul-2026
Riya15-Feb-202515-Aug-2026
Karan10-Apr-202509-Apr-2026
Simran01-Jun-202501-Dec-2026

Excel Formula:

Enter this formula in D2:

=DATEDIF(B2,C2,"M")

Expected Result:

Employee NameComplete Months
Aman18
Riya18
Karan11
Simran18

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 NameStart DateCompletion Date
Website Design01-Jan-202615-Jan-2026
Mobile App05-Feb-202625-Feb-2026
Database Setup10-Mar-202610-Apr-2026
Software Testing01-May-202601-Jun-2026

Excel Formula:

Enter this formula in D2:

=DATEDIF(B2,C2,"D")

Expected Result:

Project NameTotal Days
Website Design14
Mobile App20
Database Setup31
Software Testing31

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 NameJoining DateCalculation Date
Rahul15-Jan-202020-Mar-2026
Priya01-Feb-202101-May-2026
Amit10-Jun-202210-Sep-2025

Excel Formula:

Enter this formula in D2:

=DATEDIF(B2,C2,"YM")

Expected Result:

Employee NameRemaining Months
Rahul2
Priya3
Amit3

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 NameStart DateEnd Date
Project A01-Jan-202610-Mar-2026
Project B05-Feb-202615-Apr-2026
Project C10-Jun-202620-Aug-2026

Excel Formula:

Enter this formula in D2:

=DATEDIF(B2,C2,"MD")

Expected Result:

Project NameRemaining Days
Project A9
Project B10
Project C10

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 NameStarting DateEnding Date
Rahul15-Jan-202020-Mar-2026
Priya10-Feb-202125-Apr-2026
Amit05-Jun-202215-Aug-2026

Excel Formula:

Enter this formula in D2:

=DATEDIF(B2,C2,"YD")

Expected Result:

Employee NameDays Ignoring Years
Rahul65
Priya74
Amit71

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 NameDate of BirthCalculation Date
Aarav10-May-201009-Nov-2026
Riya15-Aug-201209-Nov-2026
Kabir20-Dec-201109-Nov-2026
Anaya01-Jan-200909-Nov-2026

Excel Formula:

Enter this formula in D2:

=DATEDIF(B2,C2,"Y")

Expected Result:

Student NameCompleted Age
Aarav16 Years
Riya14 Years
Kabir14 Years
Anaya17 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

EmployeeJoining DateCalculation Date
Mohit01-Apr-202101-Apr-2026
Sneha15-Jul-202220-Jul-2026
Varun10-Oct-202309-Oct-2026

Calculate completed years of experience.

Task 2: Calculate Complete Months

CustomerRegistration DateCalculation Date
Customer A01-Jan-202601-May-2026
Customer B15-Feb-202614-Jun-2026
Customer C10-Mar-202610-Sep-2026

Calculate complete months for each customer.

Task 3: Calculate Total Days

ProjectStart DateEnd Date
Project X01-Feb-202620-Feb-2026
Project Y10-Mar-202610-Apr-2026
Project Z05-Jun-202625-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 NameJoining DateCalculation Date
Rahul15-Jan-202020-Mar-2026
Priya10-Feb-202125-Apr-2026
Amit05-Jun-202215-Aug-2026
Neha01-Sep-202310-Nov-2026

Your Tasks:

  1. Calculate complete service years using "Y".
  2. Calculate complete service months using "M".
  3. Calculate total service days using "D".
  4. Calculate remaining months using "YM".
  5. Calculate remaining days using "MD".

Expected Report Structure:

Employee NameService YearsService MonthsTotal DaysRemaining MonthsRemaining Days
RahulYour ResultYour ResultYour ResultYour ResultYour Result
PriyaYour ResultYour ResultYour ResultYour ResultYour Result
AmitYour ResultYour ResultYour ResultYour ResultYour Result
NehaYour ResultYour ResultYour ResultYour ResultYour 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.