ThisCalculator

Everyday

Calculate Months Between Two Dates in Excel

Updated · Standard formula, shown below

Calculate Months Between Two Dates in Excel

This guide shows you how to calculate the number of months between two dates in Excel using several built-in functions. You’ll learn the DATEDIF function for complete calendar months, the YEARFRAC function for fractional months, and a simple formula for whole months with rounding. No prior Excel expertise is needed—just a spreadsheet with two date cells.

What You’ll Need

  • Microsoft Excel (2010 or later, including Microsoft 365)
  • Two date values entered in separate cells (e.g., A1 and B1)
  • Dates formatted as dates, not text

Step-by-Step

  1. Enter your dates – Put the start date in cell A1 and the end date in cell B1. For example, type 01/15/2023 in A1 and 06/20/2024 in B1.
  2. Check date formatting – Select both cells, go to the Home tab, and ensure the Number Format is set to Date. If Excel shows a number, change the format to Date.
  3. Use DATEDIF for complete months – In cell C1, type: =DATEDIF(A1, B1, "m") and press Enter. This returns the number of full months between the two dates. For example, from Jan 15, 2023 to Jun 20, 2024 gives 17.
  4. Use DATEDIF for months with days – If you want the result as “X months, Y days”, type in C2: =DATEDIF(A1, B1, "ym") to get the leftover days after full months. Combine with the previous step: =DATEDIF(A1,B1,"m") & " months, " & DATEDIF(A1,B1,"ym") & " days".
  5. Use YEARFRAC for fractional months – In cell D1, type: =YEARFRAC(A1, B1)*12 and press Enter. This gives the exact number of months as a decimal (e.g., 17.16). Format the cell to show 2 decimal places if needed.
  6. Round to nearest whole month – For a rounded count, use: =ROUND(YEARFRAC(A1, B1)*12, 0) in cell E1. This rounds 17.16 to 17.
  7. Use a simple formula for whole months (alternative) – If you want to count months regardless of days, use: =(YEAR(B1)-YEAR(A1))*12 + MONTH(B1)-MONTH(A1). This returns 17 for the example, but note it ignores days (even if the end day is earlier).

Common Mistakes

  • Using DATEDIF with the start and end reversed – DATEDIF requires the start date to be earlier than the end date. If reversed, it returns a #NUM! error.
  • Dates entered as text – If you type dates with slashes but Excel treats them as text (e.g., because of regional settings), calculations fail. Use the DATE function: =DATE(2023,1,15).
  • Forgetting that DATEDIF counts complete months only – For Jan 31 to Feb 28, DATEDIF returns 0 because February doesn’t have a 31st. This is expected, but many users expect 1.
  • Using the simple formula when you need actual month boundaries – The formula (YEAR(B1)-YEAR(A1))*12 + MONTH(B1)-MONTH(A1) counts month numbers, not full months. For Jan 31 to Feb 1 it returns 1, but DATEDIF returns 0.
  • Not formatting the result – If the result appears as a date (e.g., 1/15/1900), change the cell format to Number.

Tips

  • Use DATEDIF when you need the number of complete calendar months, such as for lease terms or subscription billing.
  • Use YEARFRAC when you need a precise fractional month count for interest calculations or prorating.
  • To count months including the start month, add 1 to the DATEDIF result: =DATEDIF(A1,B1,"m") + 1.
  • For a text label like “17 months”, use: =DATEDIF(A1,B1,"m") & " months".
  • If you need to handle dates in different years, DATEDIF works, but ensure the end date is always later.

Troubleshooting

  • #NUM! error – The start date is after the end date. Swap the cell references.
  • #VALUE! error – One of the cells is not a valid date. Check for text or misformatted numbers. Use the DATE function to create dates.
  • Result shows as a date – The cell is formatted as Date. Right-click, select Format Cells, and choose Number or General.
  • DATEDIF not appearing in the function list – DATEDIF is hidden in Excel’s function library, but it still works. Type it manually.
  • Getting 0 for close dates – DATEDIF returns 0 if the dates are within the same month. Use YEARFRAC to get a decimal instead.

Frequently Asked Questions

Does DATEDIF count the start month?

No. DATEDIF counts complete months, meaning the month of the start date is only counted if the end date falls on the same day or later in a subsequent month. For example, Jan 15 to Feb 14 returns 0, but Jan 15 to Feb 15 returns 1.

How do I calculate months between dates as a decimal?

Use =YEARFRAC(start_date, end_date)*12. This gives the exact number of months, including fractions, based on a 365-day year.

Why does my simple formula give 1 for Jan 31 to Feb 1?

Because the formula (YEAR(B1)-YEAR(A1))*12 + MONTH(B1)-MONTH(A1) subtracts month numbers (2-1=1), ignoring days. For full months, use DATEDIF.

Can I calculate months between dates in Google Sheets?

Yes, the same functions work in Google Sheets: DATEDIF, YEARFRAC, and the simple formula all behave similarly.

How do I get the result as “X years, Y months”?

Use two DATEDIF calls: =DATEDIF(A1,B1,"y") & " years, " & DATEDIF(A1,B1,"ym") & " months".

What if my dates include times?

DATEDIF ignores time entirely. If you need to account for time, use =DATEDIF(A1,B1,"m") + (MOD(B1,1) < MOD(A1,1)) to subtract a month if the end time is earlier in the day.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.