How to Calculate Duration Between Two Dates in Excel with the DATEDIF Function

Managing time-based data in Excel often requires calculating the duration between two dates. The DATEDIF function is a powerful yet underutilized tool that simplifies this process. Whether you need to determine the difference in years, months, or days, DATEDIF can help you extract precise time durations automatically.

This guide will walk you through how to use the DATEDIF function effectively in different scenarios.

Understanding the DATEDIF Function

The DATEDIF function calculates the difference between two dates in various units. And you don’t need a spreadsheet calendar to do it.

The syntax of this formula is:

=DATEDIF(start_date, end_date, unit)

• start_date – The earlier date in the range.
• end_date – The later date in the range.
• unit – Specifies whether to calculate the difference in years (“Y”), months (“M”), or days (“D”).

That means you enter =datedif, then an open parenthesis sign, then click the cell that starts your calculation. Then click the cell that ends it. Then enter Y, M, or D to calculate years, months, or days.

Basic Usage of DATEDIF

If you want to calculate days, months, or years between two dates in your spreadsheet, you’ll want to use DATEDIF. Here’s how to do each of those calculations.

Calculating Years Between Two Dates

To determine the number of full years between two dates, use:

=DATEDIF(A2, B2, "Y")

For example, if an employee was hired on June 15, 2019, and today’s date is February 25, 2024, this formula will return 4 (full years).

Calculating Months Between Two Dates

To find out the total number of months between two dates, modify the function:

=DATEDIF(A2, B2, "M")

If the start date is June 15, 2019, and the end date is February 25, 2024, the result will be 56 months.

Calculating Days Between Two Dates

To determine the exact number of days:

=DATEDIF(A2, B2, "D")

If the start date is June 15, 2019, and the end date is February 25, 2024, the result would be 1,715 days.

Advanced DATEDIF Usage: Combining Years, Months, and Days

In some cases, you might want to break down the time difference into years, months, and days, rather than showing the total duration in a single unit.

Step 1: Extract Years

Use:

=DATEDIF(A2, B2, "Y") & " years"

Step 2: Extract Remaining Months Within the Final Year

Instead of counting total months, use:

=DATEDIF(A2, B2, "YM") & " months"

This formula calculates the number of months left over after full years have been counted.

Step 3: Extract Remaining Days Within the Final Month

To find the exact day difference within the last month, use:

=DATEDIF(A2, B2, "MD") & " days"

Step 4: Combining Everything

To return a neatly formatted duration like “4 years, 8 months, 10 days”, use this formula:

=DATEDIF(A2, B2, "Y") & " years, " & DATEDIF(A2, B2, "YM") & " months, " & DATEDIF(A2, B2, "MD") & " days"

Key Differences in Units

Common Mistakes & How to Fix Them

1. Incorrect Order of Dates
   • If the start date is later than the end date, DATEDIF will return an error.
   • Solution: Ensure start_date is earlier than end_date.
2. Using DATEDIF Without Quotes for Units
   • Incorrect: =DATEDIF(A2, B2, Y)
   • Correct: =DATEDIF(A2, B2, "Y")
3. Negative Results for Partial Units
   • If using "MD" or "YM" without "Y", results may be misleading.
   • Always combine with "Y" and "M" where necessary.

Why Use DATEDIF?

• Quickly calculates differences between hire dates and today’s date for tenure tracking.
• Helps analyze customer subscriptions, project durations, and product lifecycles.
• Converts raw date data into meaningful insights with years, months, and days breakdowns.

DATEDIF allows you to quickly and accurately calculate the time between two dates in Excel. There’s a similar option in other popular spreadsheet software too. We’ve got a whole guide on it. Here’s how to calculate days between dates in Google Sheets.