How to Calculate Months Between Dates in Excel

By Jon Saldivar, owner of Simple Sheets. Month counts drive the schedules inside our budget, Gantt and production planning templates, so the differences between these methods are ones we have had to get exactly right.
Last updated: August 18, 2026

Do you need to calculate the number of months between two dates in Excel?

You may need this when tracking project timelines, calculating loan durations, working out tenure, or building a payment schedule. Excel gives you at least four ways to do it, and they do not all return the same number for the same two dates. That is the part most guides skip, so this one starts there.

Which Method Should You Use? Side by Side Comparison

Here is the same set of methods applied to two date pairs. The second pair is deliberately awkward, because that is where the methods separate.

Look at the second column. One day apart, and the answers range from 0 to 1. Neither is wrong. DATEDIF says no full month has elapsed. The calendar method says you moved from December to January, so one month boundary was crossed.

Quotable rule: DATEDIF counts months you have finished living through. The YEAR and MONTH method counts month labels you have passed. Decide which one your business rule means before you pick a formula.

How Do You Use DATEDIF to Count Whole Months?

The DATEDIF function is the default answer when you only want completed months. It counts full months and ignores any remaining days that do not form another one.

Formula: =DATEDIF(start_date, end_date, "m")

Where:

• start date: the earlier date.

• end date: the later date.

• "m": the unit for full months.

Worked example

To calculate the number of months between January 15, 2023 and August 20, 2023, use the formula above with your cell references.

Formula: =DATEDIF(A1, B1, "m")

That returns 7, meaning 7 full months have passed. The extra 5 days are discarded rather than rounded.

Why does DATEDIF not appear in Excel's function list?

Because Microsoft does not document it in the normal way. DATEDIF is a compatibility function carried over from Lotus 1-2-3. It works in every modern version of Excel, but it does not appear in the Insert Function browser and it gives you no argument tooltip while you type. You have to type the whole thing, including the quotation marks around the unit, from memory.

It is also fussy about arguments. Passing the later date first returns an error rather than a negative number, which is the opposite of what a simple subtraction does.

Quotable rule: DATEDIF exists, works everywhere, and autocompletes nowhere. If Excel is not offering to help you finish the formula, you have spelled it right.

What Do the DATEDIF Units m, ym and md Mean?

The third argument decides what unit you get back, and the three-letter combinations are where people go wrong. Using the same 15 Jan 2023 to 20 Aug 2023 example:

The difference between "m" and "ym" only shows up once you pass a year. Over a span of 2 years and 7 months, "m" returns 31 while "ym" returns 7. Use "m" when you want one total number, and "y" plus "ym" plus "md" when you want to write out a readable duration.

Microsoft flags "md" as capable of returning a negative or inaccurate result in certain month-end combinations, so avoid it for anything contractual and use a days-based calculation instead.

Quotable rule: "m" is the running total. "ym" is the remainder after years. Mixing them up is the most common DATEDIF mistake.

How Do You Count Months Between Dates Without DATEDIF?

Plenty of people would rather not rely on an undocumented function, especially in a workbook other people will maintain. The standard replacement is built from YEAR and MONTH.

Formula: =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)

Reading it left to right: work out how many years apart the two dates are and multiply by 12, then add the difference in month numbers. The day of the month never enters the calculation.

For 15 January 2023 to 20 August 2023 that is (2023 minus 2023) times 12, plus 8 minus 1, which gives 7. Same as DATEDIF for this pair.

Making it behave like DATEDIF

If you want the manual formula to match DATEDIF exactly, subtract one when the end day of the month has not yet reached the start day:

=(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)-(DAY(B2)<DAY(A2))

That final bracket returns TRUE, which Excel treats as 1, whenever the month is not yet complete. Subtracting it drops the partial month, which is precisely what DATEDIF does.

Other DATEDIF-free options

• Simple subtraction: =(B2-A2)/30.44 gives an approximate month count using the average month length. Fine for rough estimates, not for contracts.
• YEARFRAC: =YEARFRAC(A2,B2)*12 avoids DATEDIF entirely and gives you decimals, covered in full below.

Quotable rule: the YEAR and MONTH formula is DATEDIF's calendar-month cousin. Add the DAY comparison at the end and it becomes DATEDIF's twin.

Whole Months vs Calendar Months: The Difference That Causes Wrong Answers

This distinction is the reason two people can both be right and get different numbers, so it is worth stating plainly.

• A whole month is elapsed duration. From 15 March to 14 April is not a whole month. From 15 March to 15 April is. This is what DATEDIF with "m" measures, and it is the right basis for loan terms, subscriptions, notice periods and anything where the anniversary date matters.
• A calendar month is a label on the calendar. March to April is one month regardless of which day you started. This is what the YEAR and MONTH formula measures, and it is the right basis for reporting periods, budget columns, invoice cycles and anything where you fill in a monthly grid.

The clearest test case is 31 December to 1 January. Elapsed duration: one day, so zero whole months. Calendar labels: December to January, so one month. Both answers are defensible and they differ by 100 percent of the value.

A quick way to decide: if someone would object to being billed, use whole months. If you are filling in a column per month on a report, use calendar months.

Quotable rule: whole months answer "how long has it been". Calendar months answer "how many months does this span". Pick the question first, then the formula.

Read more: How to Change Date Format in Excel.

How Do You Count Partial or Decimal Months?

The YEARFRAC function returns the proportion of a year between two dates as a decimal. Multiply by 12 and you have months including the partial one.

Formula: =YEARFRAC(start_date, end_date) * 12 

Where:

• YEARFRAC(start_date, end_date) returns the number of years between two dates as a decimal.

• Multiplying by 12 converts years to months.

Worked example

Calculating complete and partial months between 15/01/2023 and 20/08/2023:

The result is roughly 7.17, meaning 7 full months plus about 17 percent of another one.

The basis argument, and why your decimal may differ

YEARFRAC takes an optional third argument that controls how days are counted. Leave it out and Excel uses basis 0, the US 30/360 convention, which pretends every month has 30 days. That is why YEARFRAC results can look slightly off against a plain day count.

• 0 or omitted: US 30/360
• 1: Actual days over actual year length, the most intuitive for general use
• 2: Actual days over 360
• 3: Actual days over 365
• 4: European 30/360

For a decimal that matches real elapsed time, use =YEARFRAC(A2,B2,1)*12.

Quotable rule: YEARFRAC defaults to a 30-day month. Add 1 as the third argument when you want actual days counted.

How Do You Get Months and Days Together?

To read out the full duration rather than one number, combine two DATEDIF calls: one for whole months and one for the leftover days.

Formula: =DATEDIF(start_date, end_date, "m") & " months and " & DATEDIF(start_date, end_date, "md") & " days"

Where:

• DATEDIF(start_date, end_date, "m") returns the full months between the two dates.

• DATEDIF(start_date, end_date, "md") returns the days left over after those full months.

Worked example

Months and days between 15/01/2023 and 20/08/2023:

The formula reports 7 full months and 5 extra days between the two dates.

For a three-part version covering years as well, use "y" then "ym" then "md":

=DATEDIF(A2,B2,"y") & " years, " & DATEDIF(A2,B2,"ym") & " months, " & DATEDIF(A2,B2,"md") & " days"

Remember that the result is text, not a number, so you cannot sum a column of these. Keep a numeric month column alongside if you need to total or chart it.

Quotable rule: use "y", "ym", "md" in that order for readable durations. Never "y", "m", "d", which double counts.

How Do You Round Partial Months Up or to the Nearest Month?

Rounding up so any part of a month counts as a full month

When planning or billing, a partial month often has to count as a whole one. Counting months across a capacity horizon is also the basis of any production planning template, where each month is a planning bucket.

Formula: =ROUNDUP(YEARFRAC(A1, B1) * 12, 0)

Where:

• YEARFRAC(A1, B1) calculates the fraction of a year between the two dates.

• YEARFRAC(A1, B1) * 12 converts that fraction into months, for example 0.599 times 12 equals 7.19.

• ROUNDUP(...) rounds 7.19 up to 8, so any extra days count as a full month.

Rounding to the nearest whole month

To round to the nearest month rather than always down (DATEDIF) or always up (ROUNDUP), use ROUND.

Formula: =ROUND(YEARFRAC(A1, B1) * 12, 0)

If YEARFRAC(A1, B1) * 12 equals 7.5, this returns 8.

Put simply: DATEDIF always rounds down, ROUNDUP always rounds up, and ROUND splits the difference at the halfway point.

Read more: How to Add Month to Date in Excel.

Why Does My Formula Return #NUM!, a Negative Number or #VALUE!?

#NUM! error: the dates are the wrong way round

If the start date is later than the end date, DATEDIF returns a #NUM! error rather than a negative number. Unlike ordinary subtraction, DATEDIF refuses to run backwards.

Two fixes depending on what you want:

• Show a message: =IFERROR(DATEDIF(A1, B1, "m"), "Invalid date range")
• Handle either order automatically: =DATEDIF(MIN(A1,B1), MAX(A1,B1), "m")

That MIN and MAX wrapper is the better default in any template where users type dates in whatever order they think of them.

Negative results

The YEAR and MONTH formula does return negatives when the dates are reversed, which is often useful. Wrap it in ABS if you only care about the size of the gap: =ABS((YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2))

#VALUE! error: the dates are stored as text

Date functions need real Excel date serial numbers. Values pasted from a web page, a PDF or a CSV are often text that merely looks like a date.

How to spot it: a real date sits right-aligned in the cell by default, text sits left-aligned. Selecting the column and checking whether the status bar shows a Sum is another quick test.

How to fix it:

• Text to Columns. Select the column, Data tab, Text to Columns, Next, Next, choose Date and the matching order such as DMY, then Finish. This is the fastest fix for a whole column.
• DATEVALUE. Use =DATEVALUE(A2) in a helper column, then format the result as a date.
• Check for spaces. Wrap in TRIM first if the imported text has trailing spaces: =DATEVALUE(TRIM(A2)).

Quotable rule: if a date is left-aligned in its cell and you have not centred it yourself, Excel thinks it is text and every date formula will fail on it.

How Do You Go the Other Way With EDATE and EOMONTH?

Counting months backwards is only half the job. The reverse operation, adding months to a date, uses two different functions.

EDATE handles month ends sensibly. =EDATE("31-Jan-2024",1) returns 29 February 2024 rather than an invalid 31 February, and it respects leap years.

Use negative numbers to go backwards. =EDATE(A2,-6) gives the date six months before A2, which is how you build a rolling six-month lookback.

Quotable rule: EDATE keeps the day of the month, EOMONTH snaps to the month end. Use EOMONTH for anything that reports on a monthly cycle.

How Do You Build a Month-Count Column in a Schedule?

The most common real use of all this is a schedule where each row needs to know how many months have elapsed, or which month bucket it belongs to.

A practical setup with the project start date in $B$1:

1. Month number column. =DATEDIF($B$1, A2, "m")+1 gives Month 1 for the first month, Month 2 for the second, and so on.
2. Month label column. =TEXT(A2,"mmm yyyy") produces "Aug 2026", which sorts poorly as text but reads well in a report.
3. Bucket start date. =EOMONTH(A2,-1)+1 snaps any date to the first of its own month, which is what you group a pivot table on.
4. Remaining months. =MAX(0, DATEDIF(TODAY(), $B$2, "m")) counts down to the end date and stops at zero instead of erroring once the date passes.
5. Schedule row header. =EDATE($C$1, COLUMN()-COLUMN($C$1)) dragged across a row produces consecutive monthly column headers from a single start date.

This is exactly how a gantt chart excel template calculates task duration. If you are calculating expense periods such as reimbursement cycles or quarterly spend, an expense tracking template handles the date maths automatically.

Quotable rule: put the start date in one absolute cell and reference it everywhere. A schedule with the start date typed into forty formulas cannot be shifted.

Final Thoughts

Counting months in Excel is easy. Counting the months you actually meant is the hard part. DATEDIF gives you completed months, the YEAR and MONTH formula gives you calendar months, and YEARFRAC gives you the decimal in between. ROUND and ROUNDUP decide what happens to the leftover days.

If your number looks wrong by exactly one, you are almost certainly using the whole-month method where you meant calendar months, or the other way round.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

Frequently Asked Questions

1. How do I calculate the number of months between two dates in Excel?

Use =DATEDIF(A2,B2,"m") for completed whole months. For calendar months, use =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2). For a decimal that includes the partial month, use =YEARFRAC(A2,B2)*12.

2. How do I count months between two dates without DATEDIF?

Use =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2). To make it match DATEDIF exactly, subtract a partial month with =(YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)-(DAY(B2)<DAY(A2)).

3. Why is DATEDIF not in Excel's function list?

DATEDIF is an undocumented compatibility function inherited from Lotus 1-2-3. It works in every modern version of Excel but does not appear in the Insert Function browser and offers no argument tooltip, so you must type it in full.

4. What is the difference between DATEDIF "m" and "ym"?

"m" returns the total number of complete months across the whole span. "ym" returns only the months left over after the complete years. Over 2 years and 7 months, "m" gives 31 and "ym" gives 7.

5. What is the difference between whole months and calendar months?

A whole month is elapsed duration, so 15 March to 14 April is not a full month. A calendar month is a boundary crossed, so March to April counts as one regardless of the day. From 31 December to 1 January, DATEDIF returns 0 and the calendar method returns 1.

6. How do I include partial months in the result?

Use =YEARFRAC(A2,B2)*12 for a decimal. Add 1 as the third YEARFRAC argument, as in =YEARFRAC(A2,B2,1)*12, if you want actual days counted rather than the default 30-day month convention.

7. Why does my DATEDIF formula return a #NUM! error?

The start date is later than the end date. DATEDIF will not run backwards. Wrap it as =DATEDIF(MIN(A2,B2), MAX(A2,B2), "m") to accept either order, or use IFERROR to show a message.

8. Why do my date formulas return #VALUE!?

The dates are stored as text rather than as Excel date values, which is common after importing from CSV or PDF. Text dates sit left-aligned in the cell. Fix a whole column with Data, Text to Columns, or convert individual cells with DATEVALUE.

9. How do I add months to a date instead of counting them?

Use =EDATE(A2,3) to move three months forward and keep the same day of the month, or =EOMONTH(A2,3) to land on the last day of that month. Negative numbers move backwards.

10. Which formula should I use for a loan or subscription term?

Use DATEDIF with "m" for whole completed months, which matches how terms are normally billed. Use =YEARFRAC(A2,B2,1)*12 when a partial month has to be charged pro rata.

Related Articles

How to Make a Spreadsheet Live

How to Print Labels from an Excel Spreadsheet

How to Email an Excel Spreadsheet