DATEDIF Formula Examples for Excel: Years, Months & Days

In Excel, DATEDIF calculates the time between two dates in completed years, months, or days. For example, if A2 contains January 15, 2020 and B2 contains April 23, 2023, =DATEDIF(A2,B2,"Y") returns 3, while =DATEDIF(A2,B2,"M") returns 39 and =DATEDIF(A2,B2,"D") returns 1194.

What DATEDIF Does and How Its Units Work

DATEDIF takes a start date, an end date, and a text code that tells Excel what kind of difference to return. Its syntax is =DATEDIF(start_date,end_date,unit). The function counts completed intervals: for example, the year unit returns the number of full years that have elapsed, not a rounded decimal or an estimate based on dividing days by 365.

The unit must be enclosed in straight quotation marks. Excel accepts uppercase or lowercase unit codes. The six codes are:

"Y" returns complete years between the dates.
"M" returns complete months in the entire interval, including years as groups of 12 months.
"D" returns the total number of days between the dates.
"YM" returns remaining months after complete years are removed.
"YD" returns remaining days after complete years are ignored.
"MD" returns remaining days after complete months and years are ignored. This unit has important limitations discussed below.

DATEDIF is a legacy Excel function. It remains available in Microsoft 365 for Windows, but it may not appear in formula autocomplete or the function list. That does not mean the formula is unavailable: enter it directly in a cell. The same formula syntax works in Excel for Mac and Excel for the web.

Set Up the Dates Correctly

Put the earlier date in the start-date cell and the later date in the end-date cell. For the examples below, A2 contains January 15, 2020, and B2 contains April 23, 2023. These must be real Excel date values, not text that merely looks like a date. You can enter dates in cells using a format Excel recognizes for your regional settings, or build them with the DATE function.

With the year in A2, month in B2, and day in C2, the formula =DATE(A2,B2,C2) creates a valid date from those three numbers. For example, if A2 is 2020, B2 is 1, and C2 is 15, the result is January 15, 2020. Excel stores dates as serial numbers and displays them according to the cell’s number format, so a result that looks like a number may simply need a date format. Select the cell and choose Home > Number Format to display it as a date.

When dates are reversed, DATEDIF generally returns the #NUM! error because the start date is later than the end date. Check the cell values and their order before troubleshooting the unit code.

Basic DATEDIF Formula Examples

Assume A2 contains January 15, 2020 and B2 contains April 23, 2023. Each formula below uses that same layout, and each result is a count rather than a formatted duration.

To count complete years, enter =DATEDIF(A2,B2,"Y"). The result is 3 because three full anniversaries have passed by April 23, 2023.
To count complete months across the whole interval, enter =DATEDIF(A2,B2,"M"). The result is 39: three years and three completed additional months.
To count all days, enter =DATEDIF(A2,B2,"D"). The result is 1194 days, including February 29, 2020, which falls within the interval.
To count leftover months after complete years, enter =DATEDIF(A2,B2,"YM"). The result is 3 months.
To count days remaining after complete years, enter =DATEDIF(A2,B2,"YD"). The result is 98 days, from January 15 to April 23 when the year portion is ignored.
To get the remaining day component after full months and years, enter =DATEDIF(A2,B2,"MD"). For these mid-month dates, the result is 8 days. Treat this unit cautiously, especially for dates near the end of a month.

These units answer different questions. The 39-month result is not inconsistent with the 3-year result: it expresses the same elapsed period in a different unit. Likewise, the 98 returned by YD is not the total elapsed days; D returns the total of 1194.

Show a Duration as Years, Months, and Days

A common request is to display a duration in a readable form, such as “3 years, 3 months, 8 days.” With the January 15, 2020 start date in A2 and April 23, 2023 end date in B2, use separate cells to calculate each component:

With the dates in A2 and B2, =DATEDIF(A2,B2,"Y") returns 3 complete years.
With the dates in A2 and B2, =DATEDIF(A2,B2,"YM") returns 3 additional months after those years.
With the dates in A2 and B2, =DATEDIF(A2,B2,"MD") returns 8 remaining days for this example.

You can combine the components into one text result. With the same dates in A2 and B2, =DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days" displays “3 years, 3 months, 8 days.” Because the formula joins numbers to text, the result is text, not a numeric duration suitable for arithmetic. Also, the MD component can be unreliable in some calendar situations, so verify it when dates fall near month ends or use the alternative calculation below.

For an end-of-month-safer way to calculate the days left after complete months, use EDATE to find the date reached after the completed months, then subtract that date from the end date. With A2 as January 15, 2020 and B2 as April 23, 2023, =B2-EDATE(A2,DATEDIF(A2,B2,"M")) returns 8. EDATE shifts a date by a specified number of months; here it advances January 15, 2020 by 39 months to April 15, 2023, leaving eight days. This approach is useful when you need a clear calendar-based remainder rather than relying on MD.

Calculate Age or Time Since an Event

DATEDIF is often used for a person’s age, employee service length, or time since a project began. For an age calculation, put the birth date in A2 and the date to calculate age on in B2. With a birth date of September 10, 1990 in A2 and a check date of September 9, 2026 in B2, =DATEDIF(A2,B2,"Y") returns 35. The result changes to 36 on September 10, 2026, the birthday itself. This is why subtracting birth year from current year can be wrong: it may count a birthday that has not happened yet.

To calculate age as of today rather than a date stored in B2, use =DATEDIF(A2,TODAY(),"Y"), with the birth date in A2. TODAY updates when Excel recalculates the workbook, so the displayed age can change from one day to the next. For a historical report or a fixed evaluation date, keeping the as-of date in a cell is usually easier to audit than embedding TODAY in the formula.

For a contract or service period, the same principle applies: use the actual start date and the date through which service should be counted. Define the endpoint carefully. If an agreement ends on a particular date, decide whether the calculation should include that date or measure elapsed time up to it; date subtraction and completed-period calculations describe elapsed intervals, not an inclusive count of both calendar endpoints.

DATEDIF and Leap Years or Month-End Dates

DATEDIF counts calendar intervals rather than assuming every year has 365 days. That is why a total-days result can include leap days, while a complete-years result still reflects anniversaries. A person born on February 29 presents a policy question in non-leap years: should the anniversary be treated as February 28 or March 1? Excel’s completed-year result follows its date arithmetic, but organizations may have a specific rule for legal, employment, or eligibility purposes. Do not assume a spreadsheet formula decides that policy for you.

Month-end dates require similar care. A date such as January 31 has no matching day number in February, and calendar operations may use the last day of the shorter month. DATEDIF’s MD unit has known edge-case problems and may return surprising or inaccurate values. Microsoft documentation warns users about this unit. If the exact day remainder matters, calculate completed months with M and subtract the corresponding EDATE result, then review cases where the start date is at month-end. For recurring billing or contractual periods, confirm that the chosen convention matches the governing terms.

Common Errors and How to Fix Them

#NUM!: The start date is later than the end date, or a date value is invalid. Confirm that the earlier date is first and that both cells contain valid dates.
#VALUE! or an unexpected result: One or both inputs may be text rather than dates. Re-enter the date, check regional date order, or construct it with DATE.
DATEDIF is missing from autocomplete: This is normal for this legacy function in some Excel interfaces. Type the function name and arguments manually.
A result seems too small: Y and M count only complete years or months. A period of 11 months and 29 days returns zero complete years, not one rounded year.
A result is a number rather than a date: DATEDIF returns a count, so a numeric result is expected. If you used DATE to assemble a date and see a serial number, change the cell’s number format to a date.
The formula displays literally: The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the format to General, then re-enter the formula.

Use straight quotation marks around unit codes and commas between arguments in Excel for Microsoft 365 on Windows. If your computer’s regional settings use a different list separator, Excel may show semicolons in formulas; the examples here use comma separators as standard Excel syntax. Avoid trying to fix an incorrect result by changing the cell’s display format: number formatting cannot change which intervals DATEDIF counts.

DATEDIF Compared with Date Subtraction

Simple subtraction is often the clearest choice when the question is “How many days separate these dates?” With A2 as January 15, 2020 and B2 as April 23, 2023, =B2-A2 returns 1194, the same total number of elapsed days as DATEDIF with the D unit. Subtraction is straightforward and produces a number that can be used in later calculations.

Subtraction does not directly answer “How many complete years?” or “How many complete calendar months?” Dividing the day difference by 365 or 30 gives only an approximation because calendar years and months vary in length. Choose DATEDIF for completed calendar units, subtraction for elapsed days, and EDATE when you need to move a date forward by a number of calendar months. For complicated rules such as prorated service, anniversaries on leap days, or inclusive day counts, define the rule explicitly before choosing a formula.

Frequently Asked Questions

Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is a legacy function and may not appear in autocomplete or the Insert Function list. Enter its formula manually; it is still available in Microsoft 365 Excel.

Does DATEDIF count the start date or end date as a day?
For the D unit, it returns the difference between the date serial values. For example, two consecutive dates produce 1, not 2. If you need an inclusive count of both endpoints, add 1 to the day difference after confirming that this matches your counting rule.

How do I get the total number of months, including years?
Use the M unit. With start and end dates in A2 and B2, =DATEDIF(A2,B2,"M") returns complete months across the whole interval, not just the months remaining after whole years.

Why does the MD result look wrong?
MD ignores complete months and years, and it has documented edge cases that can produce unexpected results. For a day remainder after complete months, consider subtracting EDATE of the start date by the complete-month count from the end date, and check month-end cases.

Can I use DATEDIF in Excel for Mac or the web?
DATEDIF uses the same formula syntax in Excel for Mac and Excel for the web. The interface for entering or formatting dates can vary, but the core function and its units are the same.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.