What is the DATEDIF formula in Excel?

The DATEDIF formula in Excel is a function used to calculate the difference between two dates in various units like years, months, or days. Its syntax is =DATEDIF(start_date, end_date, unit).

Why is DATEDIF not showing up in Excel's function suggestions or help files?

DATEDIF is a 'hidden' or 'undocumented' function in many versions of Excel. It was included for compatibility with Lotus 1-2-3. While it doesn't appear in the function list or typical help, it still works reliably in modern Excel versions.

How do I calculate the number of months between two dates using DATEDIF?

To calculate the total number of complete months between two dates, use the unit "M". For example, =DATEDIF(A1, A2, "M"). To find the number of months remaining after full years are accounted for, use "YM".

Can DATEDIF calculate weekends only or business days?

No, DATEDIF calculates the difference based on calendar days. It does not have built-in functionality to exclude weekends or holidays. For business day calculations, you should use Excel's NETWORKDAYS or NETWORKDAYS.INTL functions.

What is the DATEDIF formula in Excel?

The DATEDIF formula in Excel is a function used to calculate the difference between two dates in various units like years, months, or days. Its syntax is =DATEDIF(start_date, end_date, unit).

Why is DATEDIF not showing up in Excel's function suggestions or help files?

DATEDIF is a 'hidden' or 'undocumented' function in many versions of Excel. It was included for compatibility with Lotus 1-2-3. While it doesn't appear in the function list or typical help, it still works reliably in modern Excel versions.

How do I calculate the number of months between two dates using DATEDIF?

To calculate the total number of complete months between two dates, use the unit "M". For example, =DATEDIF(A1, A2, "M"). To find the number of months remaining after full years are accounted for, use "YM".

Can DATEDIF calculate weekends only or business days?

No, DATEDIF calculates the difference based on calendar days. It does not have built-in functionality to exclude weekends or holidays. For business day calculations, you should use Excel's NETWORKDAYS or NETWORKDAYS.INTL functions.

datedif excel: Calculate Date Differences Like a Pro

Master the versatile DATEDIF function to effortlessly calculate date differences in days, months, or years within your Excel spreadsheets.

Introduction to DATEDIF

The DATEDIF function in Excel is a powerful but somewhat hidden gem that helps calculate the difference between two dates in days, months, or years. Although it's often not listed in Excel’s function suggestions or official help files for all versions, it still works across most Excel installations and is widely used for various date calculations.

Understanding how to use the datedif excel formula can significantly simplify tasks like calculating age, loan durations, project timelines, and more. This guide will walk you through its syntax, units, practical examples, and common pitfalls.

Syntax of the DATEDIF Function

The basic syntax for the excel datedif function is straightforward:

=DATEDIF(start_date, end_date, unit)

DATEDIF Function Parameters
Parameter	Description
`start_date`	The earlier date in the period. This can be a date string (e.g., "01/15/2023"), a cell reference containing a date, or a date returned by another Excel function.
`end_date`	The later date in the period. Similar to start_date, it must be a valid date.
`unit`	A text string specifying the unit for the result (e.g., "Y" for years, "M" for months, "D" for days). This tells Excel how you want the difference to be expressed.

Ensure start_date is earlier than or equal to end_date to avoid errors.

Available Units for DATEDIF

The unit parameter determines how the difference is calculated and displayed. Here are the common units for the datedif formula in excel:

DATEDIF Function Units
Unit	Result
`"Y"`	Calculates the number of complete years in the period.
`"M"`	Calculates the number of complete months in the period.
`"D"`	Calculates the number of days in the period.
`"MD"`	Calculates the difference in days, ignoring months and years. Useful for finding remaining days in a month after full months are accounted for.
`"YM"`	Calculates the difference in months, ignoring days and years. Useful for finding remaining months in a year after full years are accounted for.
`"YD"`	Calculates the difference in days, ignoring years. Useful for finding days between two dates as if they were in the same year (e.g., for anniversary calculations).

Practical DATEDIF Examples

1. Difference in Complete Years

To find the number of full years between two dates:

=DATEDIF("01/01/2000", "01/01/2025", "Y")

Result: 25

2. Total Days Between Two Dates

If cell A1 contains "03/15/2024" and A2 contains "04/20/2024":

=DATEDIF(A1, A2, "D")

Result: 36

3. Combine DATEDIF for a Full Age Display (Years and Months)

If cell A1 contains a birth date (e.g., "07/20/1990") and you want to calculate the age as of today:

=DATEDIF(A1, TODAY(), "Y") & " years, " & DATEDIF(A1, TODAY(), "YM") & " months"

Example Result (if today is May 26, 2024): 33 years, 10 months

For more detailed age calculations, you can also explore our Age Calculator.

Common Errors and Fixes

#NUM! Error: This often occurs if the start_date is later than the end_date. Ensure your dates are in the correct order.
Function Not Showing in Autofill/Suggestions: DATEDIF is an undocumented function in some Excel versions. Type it out fully; it should still work.
Incorrect Date Formatting: Make sure the cells containing dates are formatted as dates, or that date strings in the formula are in a format Excel recognizes (e.g., "MM/DD/YYYY" or "YYYY-MM-DD"). You can check date validity with our Date Day Checker.
Invalid Unit: Using an unrecognized unit string (e.g., "YY" instead of "Y") will result in an error. Double-check the unit codes from the table above.

Use Cases of DATEDIF Function

The datedif function is versatile:

Calculating Age: Find a person's age in years, months, and days.
Loan Repayment Terms: Determine the duration of a loan. For more on this, see our Calculate Number of Months tool.
Work Experience Duration: Calculate total time worked at a job.
Employee Anniversaries: Identify upcoming work anniversaries.
Project Timelines: Calculate the duration of project phases.
Subscription Expiry: Find out how long until a subscription ends.

Learn more about general date information on our Calendar Date page.

DATEDIF vs. Other Excel Date Functions

Comparison of DATEDIF with Other Excel Date Functions
Feature	DATEDIF	YEARFRAC	NETWORKDAYS
Calculates full years, months, or days	✅	✓ (fractional years)	❌
Calculates business days (excluding weekends/holidays)	❌	❌	✅
Returns difference directly in "Y", "M", "D" units	✅	❌ (only years)	❌ (only days)
Calculates remaining months/days within year/month (YM, MD)	✅	❌	❌
Visible in Excel's function list by default	❌	✅	✅