Subtract Dates in Excel to Get Months

How-to basics · Updated November 20, 2026

To find the number of complete months between two dates in Excel, put the earlier date in A2 and the later date in B2, then enter =DATEDIF(A2,B2,"m") in another cell. The result counts whole months that have elapsed, not the number of calendar-month boundaries crossed. If you need that other count, use the calendar-month formula below.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows. These formulas also work in Excel for Mac and Excel for the web, although ribbon locations can differ slightly.
  • Two cells containing real Excel dates: a start date and an end date. They may be entered directly or referenced from another part of your workbook.
  • A blank cell for the result. Format it as General or Number so Excel displays a month count rather than a date.

Step-by-Step

  1. Enter the dates in separate cells. Put the earlier date in A2 and the later date in B2. For example, enter 15-Jan-2024 in A2 and 15-Apr-2024 in B2. You can type dates in a format Excel recognizes for your region, or use the date picker if one is available. Make sure Excel recognizes each entry as a date, rather than as text.
  2. Choose what you mean by “months.” There are two common answers. Completed months measures how many full months have passed from the start date. Calendar-month difference counts the change in year and month labels, without considering the day of the month. The example dates above return 3 under either method, but dates near the end of a month can produce different answers.
  3. Calculate completed months with DATEDIF. With the start date in A2 and end date in B2, enter this in C2:
    With the earlier date in A2 and the later date in B2, enter =DATEDIF(A2,B2,"m") in C2. For 15-Jan-2024 and 15-Apr-2024, the result is 3. The “m” unit tells Excel to return complete months, so an incomplete month at the end is not counted.
  4. Check the day of the month when interpreting the answer. If A2 is 15-Jan-2024 and B2 is 14-Apr-2024, the formula returns 2, because the third full monthly anniversary, 15-April, has not arrived. Change B2 to 15-Apr-2024 and the result becomes 3. This makes DATEDIF useful for elapsed time such as a subscription period or a person’s age in whole months.
  5. Use a calendar-month calculation when day numbers should not matter. With the start date in A2 and end date in B2, enter =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2) in C2. This calculates the difference between the year-and-month labels. From 31-Jan-2024 to 1-Apr-2024, it returns 3 because January to April spans three calendar-month changes, even though fewer than three full months have elapsed.
  6. Format the result as a number if needed. Select C2, then choose Home > Number Format and select General or Number. If Excel shows a date or an unexpected display, the result cell may have inherited a date format. Changing the format affects how the result appears, not the calculation itself.
  7. Apply the calculation to more rows. If each row has its own start and end dates, enter the formula in C2 and use the fill handle, the small square at the lower-right corner of the selected cell, to copy it down. Excel adjusts the row references automatically, so the next row uses A3 and B3. Check a few copied results against dates you can calculate manually.
  8. Decide how to handle dates in reverse order. DATEDIF expects the start date to be earlier than or equal to the end date. If the dates are reversed, it returns an error. Swap the dates if you want an elapsed-month count, or use a signed calendar-month calculation when the direction of the difference matters. For example, with the earlier date in B2 and later date in A2, reversing the references in the calendar formula gives a negative result.

Excel stores valid dates as serial numbers, so subtracting one date from another directly returns days, not months. There is no single fixed number of days in every month. For that reason, dividing a day difference by 30 is only an estimate; use a month-specific method when accuracy matters.

Common Mistakes

  • Expecting a day-based subtraction to return months. Subtracting the start date from the end date gives the number of days between them. A result such as 90 is not a month count, and treating every month as 30 days can be misleading.
  • Mixing up completed months and calendar months. From 31-Jan-2024 to 1-Feb-2024, zero complete months have elapsed, but the calendar-month difference is 1. Neither answer is universally right; choose based on what your report is meant to measure.
  • Using dates stored as text. A value that looks like a date may be text copied from a website or imported from another system. Excel may not calculate with it as expected. Test by changing the cell’s number format to General: a real date usually displays as a serial number, while text remains date-like text.
  • Reversing the DATEDIF arguments. The first argument is the earlier date and the second is the later date. If the first is later, DATEDIF can return #NUM!. Confirm the date order before copying the formula to a full column.
  • Misreading the result as a decimal month. DATEDIF with the “m” unit returns whole completed months. It does not return a decimal such as 2.5 for two and a half months. A decimal-month estimate needs a clearly defined convention because month lengths vary.
  • Leaving the result cell formatted as a date. Excel can display a numeric month count as a calendar date when the cell has a date format. Switch the result cell to General or Number before deciding the formula is wrong.

Tips

  • Label the result column clearly, such as “Completed months” or “Calendar-month difference.” That prevents readers from assuming the two methods mean the same thing.
  • Use actual date cells rather than typing dates inside the formula. Separate start and end date columns are easier to check, sort, update, and reuse.
  • For a quick validation, test a pair of dates on the same day of the month, then move the end date one day earlier. A completed-month result should drop by one when that date is before the next monthly anniversary.
  • If you need months and remaining days together, calculate and report those components separately rather than relying on a decimal-month conversion. Explain the convention used so the result is interpretable.
  • When entering a formula on Windows, Excel normally accepts commas between function arguments. On a computer with regional settings that use another list separator, Excel may require that separator instead. Excel for Mac uses the same functions; the exact separator can also depend on regional settings.

Troubleshooting

  • The result is #NUM!: Check that the first date in DATEDIF is earlier than or equal to the second. Also confirm that both cells contain valid dates and not text that only looks like a date.
  • The result is #VALUE!: One of the referenced cells may contain text, an error, or an unsupported value. Re-enter the dates in a format Excel recognizes. If they came from an import, use Excel’s date conversion tools or correct the source data before calculating.
  • The result is zero when you expected one: If you used DATEDIF, check whether a complete monthly anniversary has passed. For example, 31-Jan to 28-Feb does not necessarily count as one complete month under the day-based completion rule. If you mean calendar-month changes, use the year-and-month formula instead.
  • The result looks like a date: Select the result cell and choose Home > Number Format > General or Number. This is a display issue, not necessarily a formula issue.
  • The formula appears as text in the cell: The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell to General, edit the formula, and press Enter. Also check that Show Formulas is not turned on; on Windows, Ctrl+` toggles that view, and on Mac the equivalent shortcut may depend on the keyboard layout.
  • The formula does not calculate after editing a date: Check Formulas > Calculation Options and confirm that calculation is set to Automatic. If the workbook is set to Manual, choose Calculate Now or switch back to Automatic.

Frequently Asked Questions

Does subtracting dates in Excel give months automatically?
No. Direct date subtraction returns elapsed days. Use DATEDIF for completed months or the year-and-month calculation for calendar-month difference.

What does DATEDIF with “m” count?
It counts complete months between the start and end dates. It does not count a partial final month, and it does not return a decimal fraction of a month.

Why do two month formulas give different answers?
They measure different things. DATEDIF counts full elapsed months, while the calendar formula counts changes in the month and year fields. Dates close to month boundaries make the difference especially noticeable.

Can I calculate a negative number of completed months?
DATEDIF is designed for a start date before the end date, so reversing them can cause an error. Swap the dates for a nonnegative elapsed-month total. If you need direction, use a signed calendar-month calculation and make sure that measure matches your purpose.

Can I get a decimal number of months?
There is no single exact decimal-month answer that works for every date pair, because months have different lengths. Dividing days by 30 or by an average month length is an estimate. State the estimate’s convention, or report complete months and remaining days separately.

Do these formulas work in Excel for Mac and the web?
Yes. DATEDIF, YEAR, and MONTH work in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. The formulas are the same; number formatting and regional list separators may vary by device or settings.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.