Excel date difference calculation with the DATEDIF Formula

By /

Everything you need to know about the DATEDIF formula in Excel

date difference in excel formula

If you’re working with dates in Excel, one formula you’ll definitely want to have in your quick access bar is the DATEDIF formula. Whether you’re calculating date difference in excel, the age of a person, measuring the time between two events, or ageing analysis, DATEDIF is a great helper!

In this post, we’ll explain what DATEDIF is, how to use it, through some simple, yet powerful examples to help you gain in-depth understanding of this handy Excel function.

What is the DATEDIF Formula?

The DATEDIF formula calculates the date difference in excel in various format/units such as years, months, or days. It is particularly useful when you need to calculate:

  • The number of full years, months, or days between two dates.
  • Age in months, years or even in days.
  • How many months or days have passed since a specific event.
  • Calculate Subscription or Rental Periods in years, months or days.

The DATEDIF Formula

The DATEDIF formula looks like this:

=DATEDIF(start_date, end_date, unit)

    • start_date: The beginning date (earlier date).
    • end_date: The ending date (later date).
    • unit: The type of result you want. It can be “Y” for years, “M” for months, “D” for days, or a combination like “YM” (years and months) or “MD” (months and days).

Common Units that can be used in DATEDIF formula

With the dated if formula , result can be obtained in different format with use of below units:

  • “Y”: Years : get difference between two dates  in years
  • “M”: Months : get difference between two dates  in months
  • “D”: Days : get difference between two dates  in days
  • “YM”: Difference in months, ignoring the years
  • “YD”: Difference in days, ignoring the years
  • “MD”: Difference in days, ignoring the months and years

Here’s a list of common use cases of the DATEDIF formula in Excel:

1. Calculate a person’s age

  • Use Case: You can calculate someone’s age by subtracting their birthdate from today’s date.
  • Example: =DATEDIF(A2, TODAY(), "Y")
  • Use: Useful in HR for tracking employee age or for any scenario where age is required.

2. Determine the number of days between two dates

  • Use Case: Calculate the total number of days between two dates.
  • Example: =DATEDIF(A2, B2, "D")
  • Use: Can be used in project management to track time taken for tasks, or in calculating overdue payments.

3. Calculate the number of full months between two dates

  • Use Case: Find the total number of full months between two dates.
  • Example: =DATEDIF(A2, B2, "M")
  • Use: Helpful for calculating subscription durations, rental periods, or loan tenures.

4. Find the number of years and months between two dates

  • Use Case: Get the full number of years and months between two dates, with separate values for both.
  • Example: =DATEDIF(A2, B2, "Y") & " years, " & DATEDIF(A2, B2, "YM") & " months"
  • Result will be in the format : 0 years, 00 months
  • Use: Ideal for tracking work or project durations.

5. Calculate difference in days, ignoring years with DATEDIF

  • Use Case: Measure the number of days between two dates, excluding any years in between.
  • Example: =DATEDIF(A2, B2, "MD")
  • Use: Useful for understanding the gap in days between dates that fall within the same year.

6. Get the months and days, ignoring years

  • Use Case: Calculate the difference in months and days, excluding years.
  • Example: =DATEDIF(A2, B2, "YM") & " months, " & DATEDIF(A2, B2, "MD") & " days"
  • Use: Great for calculating exact periods like “3 months, 5 days” for warranties or subscription lengths.

7. Calculate days until a specific event (Using TODAY())

  • Use Case: Calculate the number of days remaining until a specific future date.
  • Example: =DATEDIF(TODAY(), A2, "D")
  • Use: Perfect for countdowns to birthdays, deadlines, or product launches.

8. Track tenure or work experience in years

  • Use Case: Calculate the number of years or months an employee has been with a company.
  • Example: =DATEDIF(HireDate, TODAY(), "Y")
  • Use: Used in HR departments to determine employee work anniversaries, benefits eligibility, or bonuses.

9. Calculate subscription or rental periods

  • Use Case: Calculate the duration of a subscription or rental period between a start date and an end date.
  • Example: =DATEDIF(StartDate, EndDate, "M")
  • Use: Helps businesses calculate subscription renewal dates or rental contract lengths.

10. Determine the duration of a loan

  • Use Case: Calculate the number of months or years between a loan’s start and end dates.
  • Example: =DATEDIF(LoanStartDate, LoanEndDate, "Y") & " years"
  • Use: Useful for financial planning and calculating interest rates or loan payments.

11. Track expiry dates

  • Use Case: Determine how many days or months are left before an item expires (e.g., contract, insurance, etc.).
  • Example: =DATEDIF(TODAY(), ExpiryDate, "D")
  • Use: Helpful for managing expiring contracts, licenses, or subscriptions.

The DATEDIF formula is a simple yet powerful tool in Excel. Whether you’re calculating someone’s age, the time between two events, or even just tracking the number of days between dates, this function can save you time and effort.

If you’ve found this guide helpful, please comment below for more tips and tricks on our blog. And remember, the more you practice, the quicker you’ll become a formula pro!

date difference in excel

Related Posts

Leave a Comment

Scroll to Top