To get the number of months between two dates in Excel, use the DATEDIF function with the “m” unit: =DATEDIF(start_date, end_date, "m"). This returns the complete calendar months between the dates, ignoring partial months. For a more precise result that includes fractional months, use =(end_date - start_date)/30.44 or the YEARFRAC function.
Understanding Date Arithmetic in Excel
Excel stores dates as serial numbers, where January 1, 1900 is 1, and each day increments by 1. This means subtracting two dates gives the number of days between them. For example, if cell A1 contains 1/1/2024 and B1 contains 3/1/2024, =B1-A1 returns 60 (because 2024 is a leap year). To convert days to months, you divide by the average number of days in a month (30.44) or use functions specifically designed for date differences.
The core challenge is that months have varying lengths (28 to 31 days), so a simple division by 30 is inaccurate. Excel provides dedicated functions to handle this properly, and the choice depends on whether you need whole months, fractional months, or calendar-month boundaries.
Using DATEDIF for Whole Months
The DATEDIF function is the classic way to calculate complete months between two dates. Its syntax is =DATEDIF(start_date, end_date, "unit"). For months, use “m” as the unit. This returns the number of complete calendar months, meaning it counts how many times the first day of a month passes between the two dates. For example:
- Start: 1/15/2024, End: 3/10/2024 → result is 1 (because February is a full month, but March is not complete).
- Start: 1/1/2024, End: 3/1/2024 → result is 2.
- Start: 1/31/2024, End: 2/28/2024 → result is 0 (since February is not a full month from Jan 31 to Feb 28).
Note: DATEDIF is a legacy function that still works in Excel 365 but is not listed in the function wizard. It returns a negative error if start_date is after end_date, so ensure the start is earlier.
Using YEARFRAC for Fractional Months
If you need a decimal number of months (e.g., 2.5 months), use YEARFRAC with the “basis” argument. The syntax is =YEARFRAC(start_date, end_date, [basis]). The basis defaults to 0 (US 30/360), but for a more accurate calendar-based calculation, use basis 1 (actual/actual). Then multiply by 12 to get months:
Assuming start in A1 and end in B1: =YEARFRAC(A1, B1, 1) * 12. For A1=1/1/2024 and B1=3/1/2024, this returns 2.0 (exactly). For A1=1/15/2024 and B1=3/10/2024, it returns approximately 1.83 months. This is useful for age calculations or prorated amounts.
Simple Division by Average Days
For a quick approximation, you can subtract the dates and divide by the average days per month: =(B1-A1)/30.44. The number 30.44 is the average days in a month (365.25/12). This gives a decimal that is close but not exact for specific month lengths. For example, from Jan 1 to Feb 1 (31 days) gives 1.02 months, which is slightly over. This method is fine for rough estimates but not for financial calculations requiring precision.
How to Choose the Right Method
- Use
DATEDIFwhen you need whole months, such as calculating tenure in years and months for employee records. - Use
YEARFRACwhen you need a precise decimal for interest calculations or age in months. - Use simple division when you need a quick, understandable estimate and the exact month boundaries don’t matter.
- If you need to count months including the start day (e.g., from Jan 1 to Mar 1 as 2 months),
DATEDIFdoes that correctly.
Common Problems and Errors
One frequent issue is the #NUM! error in DATEDIF when the start date is later than the end date. To avoid this, use =IF(A1>B1, DATEDIF(B1,A1,"m"), DATEDIF(A1,B1,"m")) to handle reversed dates. Another problem is that DATEDIF can return incorrect results for dates that fall on month-end, such as Jan 31 to Feb 28, which yields 0 months even though it’s nearly a full month. For such edge cases, consider using YEARFRAC or adding a day to the end date if you need inclusive counting.
Also, ensure your dates are actual date values, not text. If you see a date that looks like text, use the DATEVALUE function or format the cells as dates.
Comparing with Other Date Functions
Excel also has MONTH and YEAR functions that extract parts, but they don’t calculate differences. NETWORKDAYS counts working days, not months. EDATE adds months to a date, which is useful for projecting dates but not for differences. For example, =EDATE(A1, 3) gives the date 3 months after A1. To get the number of months between two dates, DATEDIF remains the most direct whole-month function.
Frequently Asked Questions
How do I get months between two dates in Excel? Use =DATEDIF(start, end, "m") for whole months, or =YEARFRAC(start, end, 1)*12 for fractional months.
Why does DATEDIF return 0 for Jan 31 to Feb 28? Because it counts complete calendar months, and from Jan 31 to Feb 28 is not a full month (Feb 28 is before Mar 1). If you need to count that as 1 month, add one day to the end date: =DATEDIF(A1, B1+1, "m").
Can I use DATEDIF in Excel 365? Yes, it works in all modern versions, though it’s not listed in the function library. You can type it manually.
What is the formula for months between two dates in Google Sheets? Google Sheets also supports DATEDIF, so the same formula works: =DATEDIF(A1, B1, "M") (uppercase M is fine).
How do I calculate age in months in Excel? Use =DATEDIF(birthdate, TODAY(), "m") for whole months, or =YEARFRAC(birthdate, TODAY(), 1)*12 for fractional.
Is there a way to get months and days separately? Yes, use =DATEDIF(A1,B1,"m") for months, and then =DATEDIF(A1,B1,"md") for days after the last full month. Combine them: =DATEDIF(A1,B1,"m")&" months, "&DATEDIF(A1,B1,"md")&" days".
Practical Example: Loan Term in Months
Suppose you have a loan start date in A1 (1/15/2024) and end date in B1 (7/20/2025). To find the total months for a report, you could use =DATEDIF(A1,B1,"m"), which returns 18 (complete months). But if you need the exact duration for interest calculation, use =YEARFRAC(A1,B1,1)*12, which returns approximately 18.17 months. Always verify your results with a calendar for critical dates.
Related
- Excel Auto Loan Amortization Template: How to Build One
- Excel Audit Process: Step-by-Step Guide
- Excel Audit Log Template: How to Build One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Balance GraphQL API: Availability and AlternativesIf you mean Balance, the B2B payments platform, its publicly described developer interface is REST-oriented; there is no…
- How to Create a Card Template in Microsoft Word for PrintingCreate a card template in Word by setting the page to the card or sheet size, building the…
- Google Gradebook Spreadsheet: Build One in Google SheetsA Google gradebook spreadsheet is a grade-tracking table you create in Google Sheets; it is not a separate…
- Excel Shortcut to Highlight All Data in a Column QuicklyIn Excel for Microsoft 365 on Windows, click the first cell in the column’s data and press Ctrl+Shift+Down…
- How to Mail Merge in Word 2010: A Step-by-Step GuideTo mail merge in Word 2010, open the Mailings tab, choose Start Mail Merge or Step-by-Step Mail Merge…
- How to Add Multiple Dropdown Menus in Google SheetsTo add multiple dropdowns in Google Sheets, select the cells that should share a menu, choose Insert >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.