Count Months Between Dates in Excel: Full and Partial Months

How-to basics · Updated November 20, 2026

To count completed months between two dates in Excel, enter the start date in A2 and the end date in B2, then put this in C2: =DATEDIF(A2,B2,"m"). It counts whole months that have elapsed, not every calendar month touched. If you mean calendar-month boundaries or a fraction of a month instead, use a different method below. These instructions are for Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel worksheet with a cell for the start date and a cell for the end date. The examples use A2 for the start date, B2 for the end date, and C2 for the result.
  • Dates Excel recognizes as real dates, rather than text that only looks like a date. You can usually enter them in a familiar local format, such as 3/15/2024, then check that Excel displays them as dates.
  • A clear definition of “months between.” For most durations, use completed months. For counts of month changes on a calendar, use calendar months instead.

Step-by-Step

  1. Set up the two date cells. In A1, enter a label such as Start date, and in B1 enter End date. Type the earlier date in A2 and the later date in B2. For a worked example, enter 15-Jan-2024 in A2 and 20-Apr-2024 in B2. The dates should be in chronological order for the standard formula.
  2. Choose what you want the result to mean. A completed-month count answers, “How many whole months have elapsed?” A calendar-month count answers, “How many month boundaries separate these dates?” Those answers can differ. From January 31 to February 1, for example, only one day has elapsed, but the dates are in different calendar months. Decide which interpretation fits your task before copying a formula down a list.
  3. Enter the completed-month formula. With the start date in A2 and end date in B2, enter this in C2: =DATEDIF(A2,B2,"m"). DATEDIF returns the number of complete months between the dates. In the example of January 15, 2024 to April 20, 2024, the result is 3: three full monthly anniversaries have passed.
  4. Check how the day of the month affects the answer. DATEDIF does not simply subtract the month numbers. From January 15 to April 14, only two complete months have elapsed, so the result is 2. The third monthly anniversary, April 15, has not yet arrived. From January 15 to April 15, the result becomes 3. This is usually the right approach for service periods, elapsed tenure, and age calculations where partial months should not round up.
  5. Use a calendar-month count if that is what your report needs. With the start date in A2 and end date in B2, enter this in C2: =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2). It compares the year and month parts, and ignores the day numbers. For January 31 to February 1, it returns 1 because the dates cross into a new calendar month. For November 2023 to February 2024, it returns 3. Use this for month-based reporting periods or counting calendar-month changes, not for measuring fully elapsed months.
  6. Fill the formula down if you have multiple date pairs. If each row has a start date in column A and an end date in column B, select C2 and use the fill handle—the small square at the lower-right corner of the selected cell—to drag the formula down. Excel adjusts the row references for each pair. Alternatively, copy C2, select the cells below it, and paste. Check a few rows manually, especially rows with dates near month-end.
  7. Format the result as a number. A month count should normally display as a whole number. Select the result cells and choose Home > Number Format > Number or General. If Excel displays a date instead of a count, the formula may be fine; the cell may simply have a date format. Change the number format, then confirm the displayed value.
  8. Save and label the method. Use a clear heading such as Completed months or Calendar months above the output. If other people will use the workbook, note which definition applies. A result of 3 is ambiguous without the dates and counting rule, particularly when a report uses calendar months but a reader expects full elapsed months.

Common Mistakes

Using the wrong definition. DATEDIF with the “m” unit counts full months. It does not count every calendar month touched by the date range. Conversely, the year-and-month calculation deliberately ignores the day, so it can report one calendar month for dates just a day apart. Neither is universally correct: choose the formula that matches the question.

Reversing the dates. The start date must be earlier than or equal to the end date in the DATEDIF formula. If B2 is earlier than A2, DATEDIF returns the #NUM! error. Sort or correct the dates, or decide whether a negative duration is appropriate for your report; DATEDIF is not a signed month-difference function.

Expecting partial months to round up. DATEDIF does not count an incomplete final month as a whole one. For example, a range that is three months and five days long returns 3, not 4. Do not add one automatically unless your business rule explicitly says any part of a month counts as a whole month.

Entering dates as text. A value that looks like a date may have been imported as text. Formulas can then return errors or unexpected results. Changing the display format alone does not convert text into a date; the underlying cell value has to be converted.

Assuming a month always has the same number of days. Calendar months have different lengths, and leap years add another complication. Dividing elapsed days by 30 is an approximation, not the same calculation as counting completed or calendar months.

Tips

Format the input cells consistently so users can spot an incorrect entry. Select A2:B2, then choose Home > Number Format > Short Date. The displayed date style depends on your Windows regional settings, but the underlying date value can still be used in formulas. To check an input, select it and look at the formula bar; if it is a recognized date, Excel stores it as a date serial number.

For a reusable table, convert your data range into an Excel table with Insert > Table. When you add rows, Excel can extend formulas and formatting automatically. Give the date columns meaningful headings, such as Start date and End date, and use an output heading that names the method. That makes it less likely that someone will confuse full months with calendar months when filtering or exporting results.

For month-end dates, test the result against the rule you intend to follow. A person whose period starts on the 31st may need a policy for February, which has no 31st day. Excel’s completed-month calculation follows its date-difference behavior, but an organization may define anniversary dates or billing periods differently. If the answer affects billing, leave, or another formal process, confirm the applicable rule rather than assuming the spreadsheet decides it.

Keep the original dates intact and put calculations in a separate column. This makes it easier to review the inputs, compare the result with a known example, and update dates later. If you need a result in years and leftover months rather than a total month count, make that a separate calculation and label it clearly; a total of 27 months and a display of 2 years, 3 months represent the same duration in different forms.

Troubleshooting

DATEDIF shows #NUM!: Check that both cells contain valid dates and that the start date is not later than the end date. A common cause is swapped columns or an imported date interpreted in the wrong month/day order. Correct the inputs, then check the formula references.

The result is #VALUE! or another unexpected error: Click each input cell and check whether Excel recognizes it as a date. If imported values are text, try selecting the affected column and choosing Data > Text to Columns, then follow the wizard and set the appropriate date order on the final step before finishing. Make a copy of the data first if the import is important, and verify the converted dates because day/month order varies by region.

The result appears as a date or a string of number signs: Select the result cell and choose Home > Number Format > General or Number. A date format can make a month count display as a calendar date. A narrow column can also show number signs for some formats; widen the column by dragging the boundary between its column letters.

The result is one month lower than expected: Check the day of the month in both inputs. If the later date has a smaller day number than the start date, the latest monthly anniversary may not have occurred yet. If your goal was to count calendar-month changes regardless of day, replace the completed-month formula with the calendar-month formula in the steps above.

The formula appears as text instead of calculating: Make sure the cell is not formatted as Text and that the formula begins with an equals sign. Change the cell format to General, then re-enter the formula or press F2 and Enter to make Excel evaluate it. If formulas are being displayed throughout the worksheet, check Formulas > Show Formulas and turn that view off.

Frequently Asked Questions

Does DATEDIF count the starting month?
No. It counts complete months that elapse from the start date to the end date. If both dates fall in the same month, it generally returns 0, even if the dates are many days apart. A calendar-month formula can return a different result when dates cross a month boundary.

Can I count months between dates in different years?
Yes. DATEDIF handles dates across years, and the calendar-month formula includes the year difference. For example, the calendar-month calculation from November 2023 to February 2024 returns 3. Make sure the year is included in each input date so Excel does not interpret it as a date in another year.

How do I count any part of a month as one month?
That is a rounding rule, not the same as completed months. Decide what qualifies as a partial month and apply that rule consistently. For instance, you might round a positive duration up, but dates, month-end handling, and whether a same-day range counts must be defined first. Avoid changing the result without documenting the rule.

How do I calculate months including a decimal?
There is no single universal fractional-month convention because months vary in length. A day-count estimate divided by 30 is approximate; other methods use actual month lengths or a financial day-count basis. Identify the required convention before choosing a formula, especially if the result is used for billing or formal reporting.

Does this work in Excel for Mac or Excel for the web?
DATEDIF and the calendar-month formula work in current Excel for Mac and Excel for the web as well as Microsoft 365 for Windows. The ribbon labels and date display can differ slightly by platform or region, but the formulas use the same cell references and comma separators.

Can I show years and leftover months instead of a total?
Yes. Use a separate display calculation or report both values, and verify it against the total month count. For clarity, keep the total-month result available as a check and label a split result as years and months; a duration of 27 total months, for example, is 2 years and 3 months.

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.