Calculating the number of months between two dates in Excel is one of those tasks that sounds simple until you actually try it. You open a worksheet, type a subtraction formula, and Excel returns a serial number that has nothing to do with months. The reason is that Excel stores dates as sequential numbers measured in days, not months, so naive arithmetic gives you the gap in days instead of the calendar units you actually want for tenure, contract length, or aging reports.
The good news is that Excel ships with several reliable ways to convert that day-based gap into months. The classic tool is the hidden DATEDIF function, which accepts a start date, an end date, and a unit code such as "M" for complete months. Beyond that you have YEARFRAC multiplied by twelve, combinations of YEAR and MONTH, and even dynamic array tricks in Microsoft 365. Each method has trade-offs around partial months, rounding, and edge cases like leap years.
This guide walks you through every approach in detail, with formulas you can paste into a sheet today. We cover whole months versus fractional months, how to handle a start date that lands on the 31st when the end month only has 30 days, and how to combine month math with conditional logic so your dashboards do not break when someone backdates a record. Along the way you will see how date functions behave alongside lookup tools, and you can review the full Excel functions list to see how DATEDIF fits into the broader family.
We will also tackle the practical questions that come up in real worksheets. How do you count months as a decimal so a part-time employee with three weeks of tenure shows up correctly? How do you treat the start and end dates inclusively versus exclusively so a one-month contract reads as one and not zero? How do you display the result as "3 years, 7 months" instead of a single integer? Each pattern below answers one of those questions with a copy-paste formula.
If you build financial models, HR dashboards, or project schedules, accurate month counts feed directly into amortization tables, accrual journals, and Gantt chart milestones. A formula that quietly returns the wrong value can throw off interest expense by hundreds of dollars or push a deadline by an entire reporting period. By the end of this article you will know which formula to reach for in which situation, why DATEDIF sometimes gives surprising answers, and how to validate your results against a known calendar.
Excel users searching for date solutions often arrive here from broader queries like vlookup excel, how to create a drop down list in excel, or how to freeze a row in excel, because date columns rarely live in isolation. They sit inside larger lookup tables, validation lists, and frozen header layouts. We will reference those patterns so you can plug the month calculation directly into your existing workflow without rewriting the whole sheet.
Finally, a quick note on versions. Every formula in this guide works in Excel 2016, 2019, 2021, Microsoft 365, and Excel for the web. A handful of dynamic array variations only run in Microsoft 365 and Excel 2021, and those are flagged clearly so you can pick a fallback. With that out of the way, let us get to the formulas themselves.
Select the columns holding your start and end dates, press Ctrl+1, and choose a Date format. Text strings that look like dates will return errors, so confirm each cell displays a real date by checking that it right-aligns automatically when you press Enter.
In an empty cell type =DATEDIF(A2, B2, "M"). Note that Excel will not autocomplete this function name because Microsoft kept it hidden for Lotus 1-2-3 compatibility. You must type the whole signature manually, including the quoted unit code.
Before trusting the formula across thousands of rows, test it with a date pair you can verify by hand. January 1, 2024 to July 1, 2024 should return exactly 6. If you see 5 or 7, check that your start date is before your end date.
Double-click the fill handle in the bottom-right of the formula cell to copy it down the column. Then scroll through and look for #NUM errors, which indicate a start date later than the end date, and zero values that may signal text masquerading as a date.
Production sheets should hide ugly error codes from end users. Wrap your formula as =IFERROR(DATEDIF(A2,B2,"M"), "") so blank or invalid rows return a clean empty string instead of #NUM, keeping your dashboard professional and easy to scan.
The most direct way to find the number of months between two dates excel offers is the DATEDIF function. Its syntax is =DATEDIF(start_date, end_date, unit) where unit is a text code in quotes. For complete months use "M". For example, =DATEDIF("1/15/2024", "7/20/2024", "M") returns 6, because six full calendar months have elapsed between mid January and mid July. The fractional portion that does not yet complete a seventh month is discarded, which matches how most people count tenure or contract age.
If you need years and remaining months separately, combine two DATEDIF calls. The formula =DATEDIF(A2,B2,"Y") & " years, " & DATEDIF(A2,B2,"YM") & " months" produces friendly output like "3 years, 7 months" suitable for HR profiles or service award letters. The "YM" code returns the months that remain after subtracting whole years, so it never exceeds 11. This concatenation pattern is the cleanest way to display compound duration in a single cell without VBA.
A common alternative uses the YEAR and MONTH functions. The formula =(YEAR(B2)-YEAR(A2))*12 + MONTH(B2)-MONTH(A2) counts month boundaries crossed, ignoring the day component entirely. That means December 31 to January 1 returns 1, even though only one day has passed. This behavior is sometimes exactly what you want for billing cycles that round to the calendar month, but it overstates duration for age or tenure calculations.
YEARFRAC offers a third path when you need decimal precision. Writing =YEARFRAC(A2, B2) * 12 returns the elapsed months as a floating-point number, useful for actuarial work and loan amortization. The optional basis argument controls the day-count convention: 0 for US 30/360, 1 for actual days in actual years, 2 for actual over 360, and 3 for actual over 365. Bond traders typically pick basis 0 while project managers prefer basis 1 for intuitive results.
For Microsoft 365 users, you can vectorize any of these formulas across a whole column with dynamic arrays. Type =DATEDIF(A2:A100, B2:B100, "M") in cell C2 and watch the spill range fill automatically. This eliminates the need to drag the fill handle and keeps your sheet faster because Excel evaluates the array once rather than recalculating every row. Combined with LET, you can name intermediate values and write more readable formulas.
One subtle point: DATEDIF counts completed units, not differences. So February 28 to March 1 in a non-leap year returns 0 months even though the month numbers differ by one. The function looks at the day component and only increments when the end date is on or after the same day of the next month. This is the most common source of confusion among new users, and it explains why DATEDIF and the YEAR/MONTH method often disagree by exactly one.
If you are building larger reports, you will likely combine month math with lookups. Many users searching for vlookup excel arrive here because they need to look up a contract start date in one table and calculate its age against today. The pattern is =DATEDIF(VLOOKUP(employee_id, contracts, 3, FALSE), TODAY(), "M"), which gives live tenure that updates every time the workbook recalculates. Just remember that TODAY is volatile and will refresh on every keystroke.
Prepare for the Microsoft Excel exam with our free practice test modules. Each quiz covers key topics to help you pass on your first try.
Whole months count only fully elapsed periods. DATEDIF with the "M" unit returns an integer, so January 15 to February 14 gives 0 because the 15th has not yet arrived in February. This convention matches how leases, mortgages, and employment tenure are typically measured. It also matches the way most courts calculate statutory deadlines, which is why legal templates rely on DATEDIF when computing notice periods.
The advantage of whole months is that the result is unambiguous and matches human intuition. The disadvantage is that you lose all information about partial months, which can matter when prorating fees or scheduling. If you need both, pair DATEDIF("M") with DATEDIF("MD") to recover the leftover days. That pair gives you a clean "3 months and 12 days" output that downstream formulas can recombine into days, hours, or any other unit you need.
Fractional months treat the gap as a continuous quantity. YEARFRAC(A2, B2, 1) * 12 returns a decimal like 6.4516, capturing both completed months and the part-month tail. This is the right approach for financial accruals, where each day generates a fraction of a month's interest. It also feeds neatly into pivot tables that need to compute weighted averages of duration across many records.
The downside is that the result depends on the day-count basis you choose, and different bases can differ by a percent or two. Always document which basis your model uses, and prefer basis 1 (actual/actual) for general-purpose work because it correctly handles leap years. Reserve basis 0 (30/360) for bond pricing or anywhere a contract explicitly cites that convention. Mixing bases across a single model is the fastest path to reconciliation pain.
Calendar month counting cares only about the month number transitions, not the day. The formula (YEAR(B2)-YEAR(A2))*12 + MONTH(B2)-MONTH(A2) treats December 31 to January 1 as a one-month gap because the month index changed. This is appropriate for billing cycles that always charge on the first of the month, regardless of mid-cycle activity.
Use this method when your business rule is calendar-based rather than duration-based. Subscription services that bill on the first, magazines that count issues, and quarterly reporting cycles all fit this model. Just be aware that it can dramatically overstate true elapsed time if your date pairs cluster near month boundaries. A spot check of edge cases is essential before deploying it in any report that drives a financial decision.
You will not find DATEDIF in the function wizard or autocomplete dropdown, yet it works perfectly in Excel 2016, 2019, 2021, Microsoft 365, and Excel for the web. Microsoft preserved it for backward compatibility with Lotus 1-2-3 spreadsheets imported decades ago. Type the full signature manually and it will always work.
Edge cases trip up even experienced Excel users when working with dates. The first one to watch is the start date that lands on the 31st when the end month only has 30 days or fewer. DATEDIF with the "M" unit handles this by checking whether the end date is on or after the same day of the next month, but the "MD" unit has well-documented bugs that can return negative numbers in this scenario. If you need day remainders, use a manual subtraction instead.
Leap years introduce subtle errors in fractional calculations. YEARFRAC with basis 1 correctly accounts for leap years by using actual days in actual years, but basis 0 forces every month to 30 days and every year to 360, distorting February calculations. If your model spans February 29, run both calculations and compare to confirm the basis you chose produces the expected result. Document the discrepancy in a hidden assumptions tab so future reviewers can audit your choices.
Text masquerading as dates is the silent killer of date math. A cell that displays "3/15/2024" might be a true date serial number or a text string, and Excel will not always warn you. The fastest diagnostic is to enter =ISNUMBER(A2) in a helper column. TRUE means you have a date, FALSE means you have text. Convert text dates with DATEVALUE or by selecting the column and running Text to Columns with the Date format option.
Time zones cause silent shifts in cross-border workbooks. Excel stores dates as local-time serial numbers with no timezone metadata, so a CSV exported from a system in Tokyo and opened in New York may show dates that are off by one. If you import from external systems, normalize all timestamps to UTC before doing month math, or add a column that records the original timezone offset so you can adjust later if needed.
Negative results from DATEDIF appear as #NUM rather than a negative number, which can mask data-entry errors in long lists. Some users wrap the call in IFERROR to suppress the error, but a better practice is to display an explicit warning: =IF(A2>B2, "Check dates", DATEDIF(A2,B2,"M")). This makes bad inputs visible during review rather than hiding them under blank cells that look fine in a printed report.
Volatile functions like TODAY and NOW force recalculation of every cell that depends on them, slowing large workbooks. If your sheet has 50,000 rows of DATEDIF(start_date, TODAY(), "M"), every keystroke triggers a full recalc. Consider replacing TODAY with a single named cell that the user updates manually, or switch to manual calculation mode under Formulas, Calculation Options. The performance gain on large models can be dramatic.
Finally, watch out for inherited regional settings. A workbook authored in the UK uses day-first formatting while a US version uses month-first. Opening the same file on different machines can swap the day and month components, silently producing wildly wrong month counts. Always store dates as ISO 8601 strings or true date serials, and avoid pasting dates as text from external sources without first converting them to a known canonical format.
Real-world use cases for the number of months between two dates excel formulas show up in nearly every department. HR teams use DATEDIF to calculate employee tenure for service awards, vesting schedules, and PTO accrual tiers. A formula like =DATEDIF(hire_date, TODAY(), "M") in a column of employee records gives instant tenure in months, and conditional formatting can then highlight anyone crossing a five-year or ten-year milestone. Sorting by this column makes anniversary planning trivial.
Finance teams rely on month counts for loan amortization, lease accounting under ASC 842, and revenue recognition under ASC 606. The number of months between contract inception and reporting date drives how much revenue gets recognized each period for ratable subscriptions. A typical formula chain calculates total contract months with DATEDIF, then divides total contract value by that number to derive monthly revenue. Errors here flow directly into financial statements, so validation is critical.
Project managers use month math to compute schedule slippage and milestone aging. The gap between baseline plan date and actual completion date, expressed in months, becomes a key performance indicator on executive dashboards. Pair this with a pivot table grouped by project owner and you have an instant accountability view. Many PMs combine this with conditional formatting that turns the gap red once it exceeds a threshold like three months.
Insurance and benefits administration uses DATEDIF for waiting periods, COBRA coverage windows, and eligibility verification. A formula that returns whole months since hire determines whether an employee qualifies for benefits open enrollment, and a fractional version drives prorated premium calculations. The compliance implications mean these formulas often get peer-reviewed before deployment, which is one reason DATEDIF remains popular despite being hidden.
Marketing and customer success teams calculate customer lifetime in months to segment cohorts and predict churn. The formula =DATEDIF(signup_date, IF(churned, churn_date, TODAY()), "M") gives current customer age regardless of status. Group these into buckets of 0-3, 4-12, and 13+ months and you have an instant view of cohort distribution. Combine with VLOOKUP into a plan-tier table for revenue per cohort. For a deeper review of related techniques, explore Excel finance functions.
Real estate and property management use month counts for lease term calculations, rent escalation triggers, and renewal notice windows. A property manager tracking 200 leases needs to know which ones reach their five-year mark in the next 90 days so renewal letters can go out on time. DATEDIF combined with EDATE solves this elegantly: compute months remaining, flag rows under 3, and email the relevant tenants from a mail-merge template.
Healthcare administration calculates patient visit intervals, drug regimen durations, and study enrollment windows. The pattern of computing months between two clinical events feeds directly into reporting required by regulators. Because the data sets are often massive, performance matters; a vectorized DATEDIF across a 100,000-row Microsoft 365 spill range can outperform a row-by-row VLOOKUP solution by orders of magnitude. Always profile your formulas on production-scale data.
Practical tips to lock in your accuracy: always build a small test grid in the upper corner of your worksheet with known date pairs and expected results. When you write a new formula, point it at this test grid first. If the test cases pass, you can trust the formula across the production data. If they fail, you have a controlled environment for debugging rather than chasing errors through thousands of rows of live data.
Use named ranges for your start and end date columns. A formula reading =DATEDIF(hire_dates, TODAY(), "M") is far easier to audit than =DATEDIF(Sheet1!$B$2:$B$5000, TODAY(), "M"). Define names under Formulas, Name Manager, and pick descriptive names that match your business vocabulary. Future reviewers and your future self will appreciate the readability gain, especially when revisiting a model after months away.
Document your day-count basis decisions in a hidden assumptions tab. Whenever you use YEARFRAC, include a comment explaining whether you chose basis 0, 1, 2, or 3 and why. This is the single most common source of model disagreement during audits. A one-line note today prevents hours of reconciliation work next quarter when someone questions a calculated total.
For dashboards consumed by non-Excel users, wrap your month counts in human-readable text. The formula =DATEDIF(A2,B2,"Y") & "y " & DATEDIF(A2,B2,"YM") & "m" displays "3y 7m" in a single cell, which fits nicely in a tight dashboard layout. Pair this with the TEXT function to apply consistent formatting, and consider using a custom number format on the underlying integer column for an even cleaner display.
Test on month-end boundaries deliberately. January 31 to February 28 is the classic trap; depending on which formula you use, you may get 0 or 1. Build deliberate test cases for the 28th, 29th, 30th, and 31st of every month transition and document the expected result for each. This catches the entire class of month-end bugs before they ever reach production. The thirty minutes you spend on this saves days of debugging later.
When sharing workbooks with international colleagues, save dates as ISO 8601 text strings in a hidden audit column. A string like "2024-03-15" parses unambiguously in every locale, whereas "3/15/2024" gets interpreted differently in UK and US Excel. Convert back to date serials only when you need to do math, using DATEVALUE. This pattern eliminates an entire class of cross-border bugs at zero performance cost.
Finally, build a personal library of validated date formulas you can copy into new workbooks. Save them as text snippets in your notes app or a master Excel template. Over time you will accumulate patterns for tenure, contract age, lease expiry, churn windows, and accrual fractions. Reaching for a pre-validated formula beats rewriting from scratch every time, both for speed and for accuracy. This habit alone separates intermediate users from genuine experts.