Yes, you can calculate how many months between two dates in Excel. But if you’re using =YEARFRAC(A2,B2)*12 or =(B2-A2)/30, you’re counting days, not months — and your HR team just approved a $14,750 overpayment on Sarah Chen’s contract extension.
The Problem
You’ve got a list of employee contract start and end dates. Finance needs exact month counts for prorated bonuses. Sales needs it for renewal tracking. And your current formula? It’s spitting out 13.97 months for a clean Jan 1 → Feb 1 period. That’s not helpful. Worse — it’s dangerous.
| Employee | Start Date | End Date | Current Formula (A2:B2) | Result |
|---|---|---|---|---|
| Sarah Chen | 2023-01-15 | 2024-02-15 | =(B2-A2)/30 | 13.00 |
| James Okafor | 2022-11-30 | 2023-02-28 | =YEARFRAC(A3,B3)*12 | 12.97 |
| Lena Park | 2023-07-01 | 2024-07-01 | =(YEAR(B4)-YEAR(A4))*12+(MONTH(B4)-MONTH(A4)) | 12 |
| Diego Mora | 2023-03-20 | 2023-04-15 | =(B5-A5)/30 | 0.87 |
| Amina Diallo | 2022-12-01 | 2023-01-01 | =YEARFRAC(A6,B6)*12 | 1.00 |
| Rajiv Mehta | 2023-05-10 | 2023-08-10 | =(YEAR(B7)-YEAR(A7))*12+(MONTH(B7)-MONTH(A7)) | 3 |
Notice anything? Lena and Rajiv get clean integers. Everyone else gets messy decimals — even when their periods are exactly one month long. That’s because those formulas treat time like a continuous fluid. But business logic isn’t fluid. Contracts start and end on calendar dates. Payroll runs monthly. You need discrete month boundaries — not day-weighted averages.
The Solution
Use DATEIF. Yes, the function Microsoft buried in legacy documentation and refuses to document properly in modern Excel help. It’s not deprecated. It’s not unreliable. It’s just quiet — and it’s exactly what you need.
Here’s how to fix your sheet in four steps:
- Type
=DATEDIF(A2,B2,"m")in cell C2. That’s it — no multiplication, no division, no YEAR/MONTH math. - Press Enter. For Sarah Chen (2023-01-15 → 2024-02-15), it returns 13 — meaning 13 full months elapsed. Not 13.97. Not 13.00 from a rough divisor.
- Drag down to fill C2:C7. No more decimals. No more confusion.
- Add error handling: wrap it like
=IF(OR(A2="",B2=""),"",DATEDIF(A2,B2,"m"))so blanks don’t return #NUM! errors.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Click C2, type =DATEDIF(A2,B2,"m") | 13 | — |
| 2 | Press Ctrl+Enter to stay in C2 | Formula stays active | Ctrl+Enter |
| 3 | Select C2:C7 → Ctrl+D to fill down | All cells updated instantly | Ctrl+D |
| 4 | Edit C2: add IF wrapper around DATEDIF | Blank inputs now show blank | F2 → edit → Enter |
Going Further
Once you’ve got DATEIF working, you’ll notice other date gaps it solves silently.
Want total months + days? Use =DATEDIF(A2,B2,"m")&" m "&DATEDIF(A2,B2,"md")&" d". For Diego Mora (2023-03-20 → 2023-04-15), that gives 0 m 26 d — not 0.87 months.
Need years + months? Try =DATEDIF(A2,B2,"y")&" y "&DATEDIF(A2,B2,"ym")&" m". Amina Diallo (2022-12-01 → 2023-01-01) becomes 0 y 1 m.
Here’s the counterintuitive part: DATEIF ignores time values. If A2 contains 2023-01-15 14:30 and B2 is 2024-02-15 09:12, DATEIF still returns 13. It truncates time automatically — which is almost always what you want for contracts, leases, and compliance reports. No need to wrap with INT() or DATEVALUE().
Also worth noting: DATEIF only works when the start date is earlier than or equal to the end date. If someone enters a future start date by mistake, you’ll get #NUM!. That’s actually useful — it surfaces data entry errors immediately instead of quietly returning negative numbers.
When NOT to Use This
Don’t reach for DATEIF if you’re calculating interest accrual, daily rate allocations, or pro-rata billing where partial months matter *by day*. For those, use actual day counts (B2-A2) and divide by the exact number of days in the relevant month — pull that with =DAY(DATE(YEAR(A2),MONTH(A2)+1,0)).
Avoid DATEIF when comparing dates across fiscal calendars that don’t align with Gregorian months — e.g., 4-4-5 retail weeks. There’s no built-in “fiscal month” unit. You’ll need custom logic with EOMONTH and WEEKDAY.
And never use DATEIF with text-formatted dates. If your ‘Start Date’ column shows 01/15/2023 but Excel sees it as text (left-aligned, no date serial number), DATEIF returns #VALUE!. Check with =ISNUMBER(A2) first. Fix with Text to Columns → Delimited → Date → MDY.
One last warning: Excel Online and Excel for iPad don’t support DATEIF. If your team uses those, stick with the safer (but messier) =YEAR(B2)-YEAR(A2))*12+MONTH(B2)-MONTH(A2)+(DAY(B2)>=DAY(A2)) — yes, that last part handles day rollover correctly.
Keyboard Shortcuts
| Task | Shortcut | Notes |
|---|---|---|
| Open Name Manager | Alt + M + M | Useful for checking named ranges used in date formulas |
| Toggle formula view | Ctrl + ` (grave accent) | See all formulas at once — spot accidental text dates |
| Fill down selection | Ctrl + D | Faster than dragging, especially on large datasets |
| Format as date | Ctrl + Shift + # | Confirms whether Excel recognizes your input as a real date |
| Evaluate formula step-by-step | Alt + M + V | Crucial for debugging nested DATEDIF or IF wrappers |
