To count complete months between two dates in Excel, enter the earlier date in A2 and the later date in B2, then use =DATEDIF(A2,B2,"m"). This returns only full months that have elapsed. If you instead need to count the calendar months touched by the dates, use the calendar-month formula below; the right method depends on what your count is meant to represent.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The formulas in this guide also work in current desktop versions of Excel and Excel for the web.
- Two cells containing valid Excel dates, such as a start date in A2 and an end date in B2. You can type dates directly into cells or refer to dates already in a table.
- A clear definition of what you want to count: full elapsed months, calendar months crossed, or calendar months included at both ends.
Excel stores real dates as numbers, even though it displays them in a date format. That means the formulas need actual date values, not text that merely looks like a date. If you are working with a list of start and end dates, use one row for each pair and copy the appropriate formula down.
Step-by-Step
-
Enter and check the dates. Put the start date in A2 and the end date in B2. For example, enter 15-Jan-2024 in A2 and 15-Apr-2024 in B2. Select each date cell and check the formula bar: Excel should recognize the entry as a date. To make the values easier to read, select the cells and choose Home > Number Format, then select a date format.
Keep the start date first and the end date second for the basic formula. If you are calculating months for many records, label the columns Start date and End date so you do not accidentally reverse them.
-
Calculate completed months. Use this method when you mean the number of whole monthly anniversaries reached, such as how long someone has been subscribed or how many full months have passed between two events. With the start date in A2 and the end date in B2, enter this formula in C2:
=DATEDIF(A2,B2,"m")For 15-Jan-2024 in A2 and 15-Apr-2024 in B2, the result is 3: the date has reached three monthly anniversaries. If B2 contains 14-Apr-2024 instead, the result is 2, because the third anniversary, 15-Apr-2024, has not yet arrived. DATEDIF counts completed months, not the number of month names appearing between the dates.
DATEDIF is a valid Excel function, although Excel may not show it in the function autocomplete or provide the same formula hints as it does for many other functions. Type it directly, including the “m” unit for months.
-
Count calendar-month boundaries instead. Use this when the question is how many month changes separate the two dates, regardless of the day of the month. With the start date in A2 and the end date in B2, enter this formula in C2:
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)For 31-Jan-2024 and 1-Apr-2024, this returns 3: the dates fall in January and April, with February and March between them. The result measures the difference between the calendar months, not the number of complete periods of a month. For dates in the same month, it returns 0, even when the dates are several weeks apart.
This calculation is useful for comparing month labels, reporting periods, or billing months when any part of a month should count as that calendar month. It is not a substitute for DATEDIF when a full month must actually have elapsed.
-
Count both the start and end calendar months. Sometimes the desired answer is the number of named calendar months that the date range touches, including both endpoints. With the start date in A2 and the end date in B2, enter this formula in C2:
=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)+1For 31-Jan-2024 through 1-Apr-2024, the result is 4 because January, February, March, and April are all included. For two dates within January, the result is 1. This inclusive count can suit a monthly report or a rule where any overlap with a month counts, but it does not mean four full months elapsed in the example.
-
Decide how to handle dates entered in reverse. DATEDIF expects its start date to be no later than its end date and can return a #NUM! error when the dates are reversed. If you want a signed number of completed months and either order is possible, with the two dates in A2 and B2, use:
=IF(B2>=A2,DATEDIF(A2,B2,"m"),-DATEDIF(B2,A2,"m"))For 15-Apr-2024 in A2 and 15-Jan-2024 in B2, the result is -3. If you want an unsigned duration instead, use the earlier date as the start and the later date as the end, or use an absolute-value approach after confirming that signed direction does not matter to your report.
-
Fill the formula down and check the output. After entering a formula in C2, press Enter and compare its result with a date pair you can verify manually. To apply it to more rows, select C2 and drag the small fill handle at the bottom-right corner of the cell down the column, or copy and paste it into the remaining result cells. Excel adjusts the row references for each pair.
Format the result as a number if Excel displays it in an unexpected date style. Select the result cells, then choose Home > Number Format > Number or General. A month count is a numeric quantity; formatting it as a date can make a correct result look wrong.
Common Mistakes
Using the wrong definition of a month. DATEDIF with the “m” unit counts complete months. The YEAR and MONTH calculation counts calendar-month boundaries. The version with +1 counts the calendar months at both ends. Those answers can differ for the same pair of dates, so decide what the result should mean before choosing a formula.
Assuming every month is a fixed number of days. Months have different lengths, and February varies in leap years. Dividing the number of days by 30 is only a rough estimate, not a reliable way to calculate calendar months or completed monthly anniversaries. Use the method that matches the rule you need.
Entering dates as text. A cell can display something date-like without containing a date value Excel can calculate with. Text imports, leading apostrophes, or date strings in an unfamiliar regional format can cause #VALUE! or inconsistent results. Check that Excel recognizes the cell as a date before troubleshooting the formula.
Expecting inclusive counts from DATEDIF. DATEDIF does not count the starting month as a whole month just because a date falls in it. It reports the number of complete months between the dates. Add 1 only when you specifically want to count the calendar months touched, and use the calendar-month formula rather than casually adding 1 to a completed-month result.
Reversing the date order or formatting the answer as a date. A start date later than the end date can produce an error with DATEDIF. Separately, a numeric result shown with a date format may appear as a date in the distant past. Confirm the date order and set the output cells to General or Number.
Tips
Write down the business rule in plain language beside your spreadsheet, such as count each calendar month touched or count only completed monthly anniversaries. This small note helps other people understand why a formula returns 3 rather than 4. It is especially useful when the count feeds a report, service period, or schedule.
Test dates around month-end before applying a formula to important data. Compare a pair such as 31-Jan-2024 and 1-Feb-2024 with a pair one day later, and check what your chosen method should return. Month-end dates can make the difference between a full-month count and a calendar-boundary count especially clear.
Use a separate result column rather than overwriting either input date. Give the output column a precise heading, such as Completed months or Calendar months included. If the workbook is shared, that label can prevent someone from interpreting one kind of count as another.
For a table with many rows, convert the range to an Excel table using Insert > Table. Formulas entered in a table column generally fill down as calculated columns when new rows are added. Check a few rows after filling to make sure the correct start and end date columns are being used.
When dates come from another system, verify their regional interpretation. For example, 03/04/2024 can mean March 4 or April 3 depending on the source format. Use an unambiguous date entry or fix the import settings before calculating, rather than trying to compensate for misread dates in the month formula.
Troubleshooting
The formula returns #NUM!. With DATEDIF, first check whether the start date is later than the end date. Swap the inputs or use the signed formula in Step 5 if reversed pairs are expected. Also check that both cells contain valid dates and that neither is blank when a result is required.
The formula returns #VALUE!. One or both inputs may be text rather than Excel date values. Select the input cells and try changing their number format to a date; if the display does not change, the values may need to be converted. For a small number of entries, re-enter the dates in a format Excel recognizes for your region. For imported data, use Data > Text to Columns and check the date format options before completing the conversion.
The answer is one month higher or lower than expected. Check whether you intended complete months, calendar-month boundaries, or inclusive calendar months. For example, 31-Jan-2024 to 1-Feb-2024 touches two calendar months but may not represent one completed monthly anniversary. Confirm the intended rule using a specific example before changing the formula.
The result looks like a date or contains decimals. A month-count formula should normally return a whole-number count. Select the result and choose Home > Number Format > General. If the formula itself uses another calculation that returns a fraction, check whether that method matches your definition; do not round a decimal into a whole month unless your rules explicitly require rounding.
The formula appears as text instead of calculating. Check whether the cell is formatted as Text or whether the formula begins with an apostrophe. Change the cell format to General, then edit the cell and press Enter to make Excel recalculate it. Also confirm that the first character is an equals sign and that the formula’s parentheses and commas are intact.
Frequently Asked Questions
How do I calculate the number of complete months between two dates?
Put the start date in A2 and the end date in B2, then use =DATEDIF(A2,B2,"m"). It counts full months reached, so a date one day short of the next monthly anniversary does not count that month.
How do I count months inclusively in Excel?
With the start date in A2 and end date in B2, use =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)+1. This counts each calendar month containing either endpoint, including both the first and last months.
Does DATEDIF count the first month?
Not as an included calendar month. Its “m” unit returns complete months elapsed between two dates. For a count of the month names touched by the date range, use the inclusive calendar-month formula instead.
Can Excel calculate months when the end date is earlier?
Yes, but the basic DATEDIF formula can return #NUM! if its start date is later than its end date. Put the earlier date first, or use the signed formula in Step 5 if you need a negative result for reversed dates.
Why does my month count differ from the number of days divided by 30?
A calendar month is not always 30 days. The day-count estimate uses a fixed-length approximation, while DATEDIF and the calendar formulas use dates and calendar boundaries. Choose the method that follows the rule for your task rather than expecting the different methods to match.
Do these formulas work in Excel for Mac or Excel for the web?
Yes. DATEDIF and the YEAR and MONTH functions are available in current Microsoft Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. Ribbon labels and layout can vary slightly by platform, but the formulas remain the same.
Related
- How to Autofit a Picture in an Excel Cell
- Autofit in Excel Without a Mouse: Keyboard Steps
- Autofit Columns in Excel: Shortcuts and Quick Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Create Mailing Labels in WordTo create mailing labels in Word, open the Mailings tab, choose Start Mail Merge > Labels, select the…
- Excel Attendance Format: A Clear Monthly Attendance TrackerAn Excel attendance format is a spreadsheet that lists each person in a row and each date in…
- How to Add a Shared Mailbox to Outlook on Any DeviceTo add a shared mailbox to Outlook, first ask your Microsoft 365 or Exchange administrator to give your…
- How to Remove Filters and Sorting in Excel QuicklyTo remove filter arrows in Excel, select a cell in the filtered range and choose Data > Sort…
- Slicer Shortcut Key in Excel: Insert and Use SlicersExcel does not have one dedicated, built-in keyboard shortcut that inserts a slicer. In Excel for Microsoft 365…
- Power Query: Find and Highlight DuplicatesPower Query can identify duplicate records, but it does not provide a cell-highlighting or conditional-formatting tool inside the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.