ThisCalculator

Everyday

Replace Excel DATEDIF: 3 Simple Formulas

Updated · Standard formula, shown below

This guide shows you how to replace Excel’s DATEDIF function with standard formulas that work in every version of Excel. DATEDIF is a hidden, undocumented function that can be inconsistent and is missing from some newer Excel releases, so using DATEDIF alternatives ensures your spreadsheets are reliable and easy to audit. You’ll learn three simple formulas that calculate the difference in years, months, and days between two dates, plus how to combine them for a full “X years, Y months, Z days” result.

What You’ll Need

  • Microsoft Excel (any recent version, including Excel 365, Excel 2021, 2019, 2016, or older)
  • Two dates entered as actual date values (not text) in separate cells
  • A basic understanding of Excel formulas (you know how to type a formula into a cell)

Step-by-Step

  1. Understand what DATEDIF does. DATEDIF returns the number of years, months, or days between two dates based on a unit argument: “Y” for complete years, “M” for complete months, “D” for days, “YM” for months ignoring years, “YD” for days ignoring years, and “MD” for days ignoring months and years. We’ll replace each of these with a standard formula.
  2. Set up your dates. Put a start date in cell A1 (e.g., 1/15/2020) and an end date in cell B1 (e.g., 3/20/2023). Make sure both are formatted as dates (right-click > Format Cells > Date). If they’re text, Excel may not calculate correctly.
  3. Replace DATEDIF for complete years (“Y”). In cell C1, enter: =YEAR(B1)-YEAR(A1)-IF(AND(MONTH(B1)<MONTH(A1),DAY(B1)<DAY(A1)),1,0). This subtracts the years, then subtracts 1 if the end date’s month/day is before the start date’s month/day (meaning the anniversary hasn’t occurred yet). For our example, this gives 3.
  4. Replace DATEDIF for complete months (“M”). In cell C2, enter: =(YEAR(B1)-YEAR(A1))*12 + MONTH(B1)-MONTH(A1) - IF(DAY(B1)<DAY(A1),1,0). This calculates total months by multiplying year difference by 12, adding the month difference, then subtracting 1 if the end day is earlier than the start day. For our example, this gives 38.
  5. Replace DATEDIF for days (“D”). In cell C3, simply enter: =B1-A1. Excel stores dates as serial numbers, so subtracting gives the number of days. For our example, this gives 1160.
  6. Replace DATEDIF for “YM” (months ignoring years). In cell C4, enter: =MOD((YEAR(B1)-YEAR(A1))*12 + MONTH(B1)-MONTH(A1) - IF(DAY(B1)<DAY(A1),1,0), 12). This takes the total months from step 4 and returns the remainder after dividing by 12. For our example, this gives 2.
  7. Replace DATEDIF for “YD” (days ignoring years). In cell C5, enter: =B1 - DATE(YEAR(B1), MONTH(A1), DAY(A1)) if the end date is after the anniversary, or =B1 - DATE(YEAR(B1)-1, MONTH(A1), DAY(A1)) if it’s before. To make it work in one formula, use: =B1 - DATE(YEAR(B1) - (MONTH(B1)<MONTH(A1) OR (MONTH(B1)=MONTH(A1) AND DAY(B1)<DAY(A1))), MONTH(A1), DAY(A1)). This calculates days from the most recent anniversary. For our example, the end date is after the start anniversary, so it gives 65.
  8. Replace DATEDIF for “MD” (days ignoring months and years). In cell C6, enter: =MOD(B1 - DATE(YEAR(B1), MONTH(B1), DAY(A1)), 30) – but this is not accurate because months have different lengths. A more accurate approach is to use the DAY function: =IF(DAY(B1)>=DAY(A1), DAY(B1)-DAY(A1), DAY(EOMONTH(B1,-1)) - DAY(A1) + DAY(B1)). This gives the day difference within the month, handling month-end correctly. For our example, it gives 5.
  9. Combine for a full “X years, Y months, Z days” result. In cell C7, enter a formula that concatenates the results: =C1 & " years, " & C4 & " months, " & C6 & " days". This gives “3 years, 2 months, 5 days” for our example.

Common Mistakes

  • Using text dates. If your dates are text (e.g., “January 15, 2020”), Excel may not compute correctly. Use DATEVALUE or enter dates as proper date values.
  • Reversing start and end dates. DATEDIF requires start date first, end date second. If you swap them, you’ll get negative numbers or errors. Our formulas assume start date is in A1 and end date in B1.
  • Forgetting to adjust for month-end. The “MD” formula is tricky when the start day is near the end of a month (e.g., Jan 31 to Feb 28). The formula given handles this, but simpler versions may give wrong results.
  • Using YEARFRAC for months. YEARFRAC gives fractional years (e.g., 3.16), not whole months. Don’t use it for month counts.

Tips

  • Use LET for readability. In Excel 365, you can use LET to define variables: =LET(s,A1, e,B1, years, YEAR(e)-YEAR(s)-IF(AND(MONTH(e)<MONTH(s),DAY(e)<DAY(s)),1,0), years). This makes formulas easier to debug.
  • Create a custom function. If you use this often, consider creating a LAMBDA function in Excel 365: =LAMBDA(start,end,unit, ... ) to mimic DATEDIF.
  • Use DATEDIF if it works. DATEDIF still works in most Excel versions, even if it’s hidden. If you’re not experiencing issues, you can keep using it. This guide is for when you need a more transparent alternative.
  • Test with known dates. Use dates like Jan 1 to Dec 31 to verify your formulas give expected results (e.g., 0 years, 11 months, 30 days).

Troubleshooting

  • #VALUE! error. This usually means your dates are text. Convert them using DATEVALUE or re-enter as dates.
  • #NUM! error. This can happen if the start date is after the end date. Check your cell references.
  • Negative results. If you get negative numbers, you likely swapped the start and end dates. Double-check the order.
  • Wrong day count. If the “YD” or “MD” results seem off, especially around month-end, verify the logic. For “YD”, the formula uses the anniversary date; if it fails, break it into two steps: first calculate the anniversary year, then subtract.
  • Formula not updating. If results don’t change when you edit dates, ensure calculation is set to Automatic (Formulas > Calculation Options > Automatic).

Frequently Asked Questions

Why is DATEDIF not in the function list?

DATEDIF is a legacy function from Lotus 1-2-3. Microsoft kept it for compatibility but never documented it in Excel’s function library. It still works in most versions, but it’s not listed in the Insert Function dialog.

Can I use DATEDIF in Excel 365?

Yes, DATEDIF works in Excel 365, but it’s still hidden. If you type =DATEDIF, it will appear in the autocomplete list, but you won’t find it in the function wizard.

What’s the difference between DATEDIF and YEARFRAC?

DATEDIF returns whole units (years, months, days), while YEARFRAC returns a decimal representing the fraction of a year. For example, between Jan 1 and Jul 1, DATEDIF with “Y” gives 0, but YEARFRAC gives 0.5.

How do I calculate age in years using a replacement formula?

Use the “Y” replacement formula from step 3. For example, if birthdate is in A1 and today’s date is in B1, the formula gives the age in complete years.

Is there a simpler way to get days between dates?

Yes, just subtract the dates directly: =B1-A1. Excel treats dates as numbers, so subtraction gives the number of days. That’s the simplest replacement.

Can I replace DATEDIF with a single formula for all units?

Yes, you can create a LAMBDA function that takes a unit argument and returns the appropriate result. But for most users, using separate formulas for each unit is clearer and easier to maintain.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.