DATEDIF in Excel: complete years, months and days

How many complete months have passed between two dates? How many days? Those are different questions. DATEDIF lets you choose the unit, which is useful when the report needs completed years or months rather than a subtraction of the year numbers printed on a calendar.

In Polish Excel, the correct name is DATA.RÓŻNICA. Reversed variants such as “RÓŻNICA.DATA” are not names of this function. I will use small synthetic examples and explain the limitation of the MD unit that Microsoft warns about.

Syntax and official documentation

DATEDIF has official Microsoft documentation. It remains available for compatibility with older Lotus 1-2-3 workbooks. An absent formula suggestion in a particular interface does not establish that the function is undocumented or unavailable.

=DATEDIF(start_date,end_date,unit)

The first argument is the beginning of the period and the second is its end. The third is a text unit code enclosed in quotation marks. English examples use commas; regional settings may require semicolons instead.

I prefer date-valued cells or DATE results over text strings. A date-looking string can be interpreted differently under different regional settings. The example constructs dates explicitly to avoid ambiguity about day and month order.

Two months and 64 days

Put January 15, 2026 in A2 and March 20, 2026 in B2. You can construct them using =DATE(2026,1,15) and =DATE(2026,3,20).

=DATEDIF(A2,B2,"M")

The result is 2 complete months. The first ends on February 15, the second on March 15. Five more days do not make another complete month. Unit Y returns zero for the same interval because a full year has not elapsed.

=DATEDIF(A2,B2,"D")

The result is 64 days. January 15 to February 15 contributes 31 days, February 15 to March 15 contributes 28, followed by five more. You can also subtract =B2-A2, because Excel stores dates as serial numbers. Format that result as a number if you want a day count.

The Polish equivalents are =DATA.RÓŻNICA(A2;B2;"M") and =DATA.RÓŻNICA(A2;B2;"D"). Unit codes remain the same when the function language changes.

Choose the unit deliberately

M and YM answer different questions. For an interval spanning several years, M gives the complete month count, while YM gives the remaining month component after years are ignored. Do not label a YM result as the total duration in months.

Likewise, YD is not the total number of days in a multi-year interval. Use D or date subtraction for the entire day difference. Select the unit according to the metric’s definition, rather than according to which number looks familiar.

Complete years depend on the anniversary

Consider another synthetic interval: March 20, 2024 to March 19, 2026. Unit Y returns 1. The second anniversary has not occurred, even though the printed year numbers differ by two.

Change the end to March 20, 2026 and Y becomes 2. This boundary case explains what “complete” means. When calculating age or service duration, the anniversary date affects the answer.

I do not divide elapsed days by 365 to obtain completed years. Years have different lengths, and rounding the resulting fraction does not automatically implement an anniversary rule. Dates involving February 29 and organizational service rules require an explicit decision about which anniversary date applies.

Why I avoid MD for a general duration breakdown

Microsoft warns that MD can produce a negative number, zero or an inaccurate result and recommends against using it. I therefore do not present a concatenation of Y, YM and MD as a universally reliable way to display years, months and days.

If you require that full decomposition, define how month ends and leap years should be handled. A simple report often needs only one clearly labeled quantity: completed months or total days. Such a result is easier to inspect and less likely to be assigned the wrong meaning.

Be especially careful with billing definitions. Complete calendar months, service days and a contractual fraction of a year can use different conventions. The function does not choose the terms of an agreement for the person preparing the report.

Errors, times and input validation

When the start date is later than the end date, DATEDIF returns #NUM!. I first investigate whether the cells were swapped or the record genuinely represents a negative interval. Replacing the error with zero would hide the difference between no elapsed time and invalid date order.

This guide concerns dates without a time component. If cells contain times too, subtraction can produce fractions of a day. Decide whether the report needs completed days, elapsed time or calendar days. Working-day calculations use other functions that account for weekends and holidays.

Blank dates and date-looking text require separate input checks. A plausible result does not prove that both cells contained the values you expected. When importing, preserve the original value and verify that conversion followed the source format.

Exercise around a monthly boundary

For a start date of January 15, 2026, compare end dates February 14, February 15 and February 16. Complete month counts should be 0, 1 and 1. With the same start and an end of March 20, M should be 2 and D should be 64.

Then reverse the dates to inspect the case that needs investigation, and label the unit in your report. If dates must be constructed from separate parts, read the entry on the DATE function.