To calculate the time between two dates in Excel, use DATEDIF. With a start date in A2 and an end date in B2, the formula =DATEDIF(A2,B2,"Y") returns the number of complete years; for example, 15-Jan-2020 to 3-Nov-2026 returns 6. To display years, months, and days together, combine the Y, YM, and MD units as shown below.
A DATEDIF example in Excel
Enter actual Excel dates in A2 and B2, with the earlier date in A2. For this worked example, A2 contains 15-Jan-2020 and B2 contains 3-Nov-2026. In a third cell, such as C2, enter this formula:
With the start date in A2 and end date in B2, this formula returns the elapsed time as separate calendar units:
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"
For these dates, the displayed result is 6 years, 9 months, 19 days. This describes completed years, then the remaining complete months, then the remaining days. The final MD component has a known limitation, so treat it carefully for unusual dates; the sections below explain the units and a safer way to think about the result.
To enter a date, use a format Excel recognizes in your regional settings, then check that it behaves as a date rather than text. A date displayed as 15-Jan-2020 is still stored as a date value when Excel recognizes it. Formatting controls how the date looks; it does not change the underlying date used in the calculation.
DATEDIF syntax and how it works
The function takes three arguments: a start date, an end date, and a unit in quotation marks. The unit tells Excel whether to count complete years, months, or days, or to return a remainder. DATEDIF is available in Excel for Microsoft 365 on Windows, although it is a legacy compatibility function and may not appear in formula autocomplete or the Insert Function list. Its absence from those menus does not mean the formula is invalid.
With the start date in A2 and end date in B2, the general syntax is =DATEDIF(A2,B2,"unit"). Replace unit with one of the supported codes, including the quotation marks. The dates must be in chronological order: if the end date is earlier than the start date, Excel returns #NUM!.
DATEDIF counts completed units rather than rounding a fractional result. For example, someone whose 30th birthday is still two days away has not yet completed 30 years. This makes the function useful for age calculations, service anniversaries, and elapsed-time reports where a partial year should not be rounded up.
What the six DATEDIF units mean
Each unit answers a different question. For the example dates above, the results are as follows:
- Y counts complete years. With A2 = 15-Jan-2020 and B2 = 3-Nov-2026,
=DATEDIF(A2,B2,"Y")returns 6. - M counts complete months across the entire interval, without breaking the count into years. For the same dates,
=DATEDIF(A2,B2,"M")returns 81. - D counts total elapsed days. For the same dates,
=DATEDIF(A2,B2,"D")returns 2483. - YM returns the remaining complete months after complete years are excluded. For the example,
=DATEDIF(A2,B2,"YM")returns 9. - YD returns the number of days after complete years are excluded. For the example,
=DATEDIF(A2,B2,"YD")returns 292. - MD returns the difference in days after complete months and years are excluded. It returns 19 for the example, but Microsoft warns that this unit can produce incorrect results in some situations.
The values answer different questions and are not interchangeable. A total of 81 months is not the same kind of output as 6 years and 9 remaining months, even though those figures are related. Likewise, 2,483 total days cannot generally be converted into years by dividing by 365 and expecting the same calendar-based answer: leap years and the positions of the dates matter.
Build a years, months, and days result
The combined formula uses three DATEDIF calls joined with the ampersand operator. The Y call returns complete years; YM returns the leftover complete months after those years; and MD supplies a leftover-day component. Text in quotation marks adds readable labels and punctuation between the numbers.
Assuming A2 contains 15-Jan-2020 and B2 contains 3-Nov-2026, use this formula in C2 to produce a single text result:
=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"
Because this formula joins numbers to text, the result is text rather than a numeric duration. That is convenient for a report or a sentence, but it is less useful for further arithmetic. If another formula needs the number of days, calculate that separately with the D unit or subtract the start date from the end date. For sorting or charting, separate numeric columns for years, months, and days may be more useful than one combined text cell.
For a simpler display, keep only the part you need. With A2 as the start date and B2 as the end date, =DATEDIF(A2,B2,"M") returns total completed months, while =DATEDIF(A2,B2,"D") returns total elapsed days. Label the result clearly: “81 months” and “2,483 days” describe the interval differently from a calendar-style age.
Entering and checking dates
DATEDIF works on Excel date values, which are stored internally as numbers. A cell can look like a date but contain text, especially if the value was imported from another system or pasted in a format that does not match the computer’s regional settings. Text dates can lead to unexpected results or errors.
Check the cells before troubleshooting the formula. Select A2 or B2 and look at the formula bar and number format; you can also temporarily set the cell format to General to see whether Excel treats the value as a date serial number. If a date is left-aligned while nearby dates are right-aligned, it may be text, though alignment alone is not conclusive because formatting can change alignment.
- Enter dates in a format recognized by your Excel settings, and confirm the month and day were interpreted as intended.
- Keep the start date and end date in separate cells, rather than embedding ambiguous date text in the formula.
- Check for extra spaces or imported apostrophes that may make a value text.
- Confirm the end date is not earlier than the start date.
- Use a date format such as d-mmm-yyyy to make the displayed date easier to inspect; changing the display format does not alter the date itself.
The steps and formula syntax are the same in Excel for Microsoft 365 on Windows and Mac. Excel for the web also supports ordinary worksheet formulas such as DATEDIF, although some interface details differ from the desktop app. Regional settings can affect how dates are entered and how function arguments are separated; the examples here use commas as separators.
Common errors and the MD limitation
#NUM! usually means the start date is later than the end date. Swap the date references if the interval is meant to run forward, or make sure your data is ordered correctly. A result that seems too small or too large may instead come from a date stored as text, a misunderstood unit, or an end date that Excel interpreted differently than intended.
A common misunderstanding is to use M when the goal is a year-and-month breakdown. M returns all complete months in the interval, so it returns 81 for the worked example. YM returns 9 because it omits the complete years. Another mistake is to assume DATEDIF rounds up partial years or months; it counts completed units.
Use MD with caution. Microsoft documents that the MD unit can result in a negative number, zero, or an inaccurate result in some scenarios. The example dates produce the expected 19-day remainder, but that does not establish that MD is reliable for every date pair, particularly around month ends or leap days. If a result will drive an important business rule, test the boundary cases in your actual data and consider calculating or validating the day remainder by a method designed for that rule, rather than assuming MD always behaves as calendar arithmetic suggests.
Leap years are another reason to test dates that matter. DATEDIF uses calendar boundaries for its year and month units; it does not simply divide the total days by a fixed number. For example, an age calculation around a February 29 birthday needs a policy for how the birthday is treated in non-leap years. Excel cannot decide that policy for you; choose the rule appropriate to the purpose and verify the formula against it.
When to use DATEDIF and when to use another method
Choose DATEDIF when the question is about completed calendar units: full years of service, completed months between dates, or total days elapsed. It is compact and can express a familiar age-style breakdown. For reporting, it is often helpful to keep the component calculations separate so a reviewer can see what each number means.
For a simple total-day difference, direct subtraction is another clear option. With A2 holding 15-Jan-2020 and B2 holding 3-Nov-2026, the formula =B2-A2 returns 2483, provided both cells contain real dates. Format the result as General or Number if it is displayed as a date. This method returns elapsed days, not a years-months-days breakdown.
DATEDIF is not a good substitute for every date calculation. It does not calculate workdays, business-day deadlines, or recurring payment periods by itself. Those tasks have different rules, such as excluding weekends or holidays, and need an approach that reflects those requirements. Similarly, a “month” can mean a calendar month, a fixed number of days, or a billing period depending on the context; decide what the intended meaning is before selecting a formula.
For birthdays and anniversaries, test dates around the relevant boundary, not just a typical mid-month example. For contracts or eligibility reports, make the inclusion rule explicit: whether the start date counts, what happens on the anniversary date, and how leap-day dates are treated. A formula can consistently apply a chosen rule, but the rule itself comes from the task.
Frequently Asked Questions
Why can’t I find DATEDIF in Excel’s function list?
DATEDIF is a legacy compatibility function, so Excel may not show it in autocomplete or the Insert Function dialog. You can still type it directly into a cell using the correct syntax and date references.
Can DATEDIF calculate someone’s age?
Yes. Put the birth date in the start-date cell and the date to calculate age on in the end-date cell, then use the Y unit to count completed years. For a current age, use a date value that updates as needed; also decide how your reporting rule handles February 29 birthdays.
What is the difference between M and YM?
M returns all complete months between the dates. YM returns only the leftover complete months after full years are excluded. In the example, M is 81 and YM is 9.
Why does DATEDIF show #NUM!?
The most common cause is an end date earlier than the start date. Check the date order and make sure both cells contain valid Excel dates rather than text.
Is DATEDIF MD safe for every date range?
No. MD can return incorrect, zero, or negative results in some cases, according to Microsoft’s documentation. It gives 19 for the example shown, but test month-end and leap-day cases before relying on it for other ranges.
How do I get the total number of days between dates?
Use DATEDIF with the D unit, or subtract the start date from the end date. Both approaches return elapsed days when the cells contain valid dates; the result for 15-Jan-2020 through 3-Nov-2026 is 2,483 days.
Related
- Excel Circle Bullets: How to Add, Format, and Align Them
- 4-Segment Circle in Excel: How to Make One
- Excel Enum Cell: What It Means and How to Create One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Random Selection From a List: GuideTo return one random item from a contiguous list in Excel, use =INDEX(A2:A20,RANDBETWEEN(1,ROWS(A2:A20))). If your items are in…
- XLOOKUP Case Sensitivity: How Excel Matches Text ValuesNo. In Excel for Microsoft 365, XLOOKUP is not case-sensitive: it treats text such as “Maple,” “MAPLE,” and…
- Q1 and Q3 in Excel: A Formula for Finding QuartilesTo find the first and third quartiles in Excel, use QUARTILE.INC with quartile numbers 1 and 3. If…
- Excel SUM Function: Does It Count Filtered Values?Yes. In Excel for Microsoft 365, SUM adds every numeric value in its referenced range, including values in…
- Excel Formula Greyed Out? Causes and FixesIf Excel’s formula commands are greyed out, first press Esc to leave cell-edit mode, then select a worksheet…
- Excel Formula: Check If a Value Is Between Two NumbersIf the value is in A2, the lower limit in B2, and the upper limit in C2, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.