How to Calculate Months Between Two Dates in Excel

The quickest way to calculate the number of months between two dates in Excel is the DATEDIF function: =DATEDIF(A2,B2,"m"), where A2 is the start date and B2 is the end date. It returns the number of complete months between them. If you need a different kind of answer, such as months that ignore the day, a decimal number of months, or years and months together, there is a formula for each, covered below.

Quick Answer

Put the start date in A2 and the end date in B2, then enter =DATEDIF(A2,B2,"m") in C2 and press Enter. The start date must be earlier than the end date, or Excel returns a #NUM! error.

Example Data Used in This Guide

All examples use a start date of January 15, 2025 in cell A2 and an end date of June 10, 2026 in cell B2. The different methods give different answers for these dates, which shows why choosing the right formula matters:

• DATEDIF complete months: 16
• Calendar months, ignoring days: 17
• YEARFRAC decimal months: 16.83
• Years and months: 1 year, 4 months

Method 1: Count Complete Months With DATEDIF

1. Type the start date in A2 and the end date in B2. Make sure Excel recognizes them as dates; they should be right-aligned by default. Left-aligned dates need fixing first; changing the date format in Excel sorts that out.
2. Click C2 and type =DATEDIF(A2,B2,"m").
3. Press Enter. Excel returns 16.
4. To apply the formula to more rows, drag the fill handle in the bottom-right corner of C2 down the column.

DATEDIF counts only full months. From January 15, 2025 to June 15, 2026 is 17 months, but June 10 falls five days short, so the answer is 16. This is the method to use for things like age in months, tenure, or how many full billing cycles have passed.

DATEDIF is a legacy function kept for compatibility with Lotus 1-2-3. It works in all current versions of Excel, Excel for the web, and Google Sheets, but Excel does not show it in formula AutoComplete or the Insert Function list, so you need to type it in full.

Method 2: Count Calendar Months, Ignoring the Day

Sometimes you want to know how many month boundaries the dates span, regardless of the day of the month. For example, a project that runs from January 15 to June 10 touches January through June. Use: If you only need the month of a single date rather than a count, converting a date to a month covers MONTH, TEXT and month-only formats.

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

For the example dates, this returns 17: twelve months for the year difference, plus six minus one. To count both the first and last month inclusively (January 2025 through June 2026 is 18 months), add 1 to the end of the formula.

Method 3: Get Months as a Decimal With YEARFRAC

For prorating costs, interest, or rent, you may need a fractional number of months. YEARFRAC returns the fraction of a year between two dates, so multiply it by 12:

=YEARFRAC(A2,B2)*12

With the default 30/360 basis, the example returns about 16.83. YEARFRAC has an optional third argument for the day-count basis. Use =YEARFRAC(A2,B2,1)*12 for actual days and actual year length, which is often preferred for financial calculations. Wrap the formula in ROUND to control decimal places, for example =ROUND(YEARFRAC(A2,B2)*12,1).

Method 4: Show Years and Months Together

To display a result like “1 year, 4 months,” combine two DATEDIF units. “y” returns complete years, and “ym” returns the remaining months after those years:

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

For the example dates, this returns “1 years, 4 months.” To add days, append &", "&DATEDIF(A2,B2,"md")&" days", but be aware that the “md” unit can return inaccurate results in some cases.

Method 5: Months From a Date Until Today

Replace the end date with the TODAY function to get a running count that updates every time the workbook recalculates:

=DATEDIF(A2,TODAY(),"m")

This is useful for tracking employee tenure, the age of an account, or how long ago an invoice was issued. Need a future date instead of a count? You can add 6 months to a date in Excel or add one year to a date.

Handle Ongoing Items With a Blank End Date

In trackers, the end date is often empty until something finishes. Use today’s date when the end date is blank:

=DATEDIF(A2,IF(B2="",TODAY(),B2),"m")

Completed rows use their real end date, and open rows keep counting up each day. Add an IF around the start date as well, such as =IF(A2="","",DATEDIF(A2,IF(B2="",TODAY(),B2),"m")), so empty rows stay blank.

Calculate Months for a Whole List at Once

In Excel for Microsoft 365 and Excel 2021 or later, the YEAR/MONTH formula spills across a range. If start dates are in A2:A100 and end dates are in B2:B100, enter this in C2:

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

Excel fills the results down automatically. To find the average gap across the list, use =AVERAGE(C2:C100), or the longest gap with =MAX(C2:C100).

Fix the “1 years” Grammar

The years-and-months formula always shows “years” and “months.” To show singular words when the value is 1, use IF: =DATEDIF(A2,B2,"y")&IF(DATEDIF(A2,B2,"y")=1," year, "," years, ")&DATEDIF(A2,B2,"ym")&IF(DATEDIF(A2,B2,"ym")=1," month"," months"). For the example dates, this returns “1 year, 4 months.”

Which Method Should You Use?

• Complete months (age, tenure, contracts): DATEDIF with “m”.
• Calendar months spanned (reporting periods): YEAR/MONTH formula.
• Partial months (prorating, interest): YEARFRAC times 12.
• Readable text (reports, HR records): DATEDIF with “y” and “ym”.

Days, weeks, or hours between dates use different formulas, covered in calculating the difference between two dates and times in Excel and subtracting two dates in Excel.

Troubleshooting

#NUM! error

DATEDIF returns #NUM! when the start date is later than the end date. Swap the cells, or use a formula that handles either order: =IF(A2>B2,-DATEDIF(B2,A2,"m"),DATEDIF(A2,B2,"m")).

#VALUE! error

One of the dates is stored as text. Text dates are usually left-aligned. Convert them with =DATEVALUE(A2), or select the column and use Data > Text to Columns > Finish to make Excel re-read the values. When Excel keeps converting other entries into dates you did not want, you can stop Excel from changing numbers to dates.

The result shows as a date

If the formula cell was previously formatted as a date, the month count appears as a date such as 1/16/1900. Select the cell and choose Home > Number Format > General.

The answer is one month lower than expected

DATEDIF only counts complete months. If the end day is earlier in the month than the start day, the last partial month is not counted. For example, January 31 to February 28 returns 0. Use Method 2 if you want calendar months instead.

Different results in another country or region

Dates typed as 06/10/2026 mean June 10 in the United States but October 6 in many other regions. Check how Excel interpreted each date, or enter dates with the DATE function, such as =DATE(2026,6,10), to avoid ambiguity.

Tips

• Keep the start date in the left column and the end date in the right column so formulas copy cleanly.
• Use absolute references (for example, $B$1) if every row should be measured against the same end date.
• Wrap the formula in IFERROR, such as =IFERROR(DATEDIF(A2,B2,"m"),""), to hide errors from blank rows.
• DATEDIF works the same way in Google Sheets, so these formulas carry over if you share the file.

Frequently Asked Questions

What is the DATEDIF function?

DATEDIF calculates the difference between two dates in a unit you choose: “y” for years, “m” for months, “d” for days, plus “ym”, “yd”, and “md” for remainders. It is a legacy function that Excel still supports but does not list in AutoComplete.

Can I calculate the difference in days or years?

Yes. Use =DATEDIF(A2,B2,"d") for days (or just =B2-A2) and =DATEDIF(A2,B2,"y") for complete years.

Why does DATEDIF not appear when I type it?

Excel hides DATEDIF from AutoComplete because it is an undocumented compatibility function. Type the full formula and it will still work.

Is there an alternative to DATEDIF?

Yes. The YEAR/MONTH formula counts calendar months, and YEARFRAC multiplied by 12 returns decimal months. Both appear in AutoComplete and are fully documented.

How do I round up to the next whole month?

Use =ROUNDUP(YEARFRAC(A2,B2,1)*12,0). This counts any partial month as a full month, which is common for billing.

Can I automate this for a whole column?

Yes. Enter the formula once, then double-click the fill handle to copy it down to the last row of data. If your data is in an Excel table, the formula fills down automatically.