Stop Using DATEDIF — Try This Instead for Days Between Dates in Excel

Most Excel trainers still teach DATEDIF as the go-to for counting days between dates. They’re wrong. Microsoft never documented it, doesn’t support it, and it fails silently in Excel for Microsoft 365 when used with leap-year edge cases or array formulas. You’ve probably seen it return #NUM! on a Friday afternoon and spent 45 minutes debugging — only to find the date in A2 was text, not a serial number. Trust me, I learned this the hard way while reconciling payroll deadlines across 7 time zones.

The Problem

You get a vendor invoice list from procurement — raw, unformatted, and full of inconsistencies. Some dates are true Excel dates (like 2024-03-15), some are text strings ('03/15/2024'), and one entry even says 'TBD'. You need to know how many days elapsed between Invoice Date and Payment Date — fast — before your team signs off on Q2 accruals.

Vendor	Invoice Date	Payment Date	Days Late?
Acme Corp	2024-02-10	2024-03-05	#VALUE!
Nova Logistics	'02/22/2024'	2024-03-12	#VALUE!
Skyline Tech	2024-01-30	TBD	#NUM!
Greenfield Labs	2024-02-29	2024-03-10	#N/A
Orion Holdings	2024-03-01	2024-03-01	0

That ‘0’ in the last row? It’s the only correct result — because Excel treats dates as serial numbers. January 1, 1900 = 1. So 2024-03-01 is 45351. Subtract two serial numbers, and you get days. Simple — unless Excel doesn’t recognize one as a date.

The Solution

Forget DATEDIF. Use subtraction — but do it right. Here’s what works, every time:

1. Clean your data first. Select column B (Invoice Date), press Alt + H + F + B to open Format Cells → Number → Date. Then hit Ctrl + 1, choose ‘Date’, and click OK. If Excel says “The selected range contains mixed data types”, that’s your warning: some cells are text.
2. Force conversion with DATEVALUE. In cell D2, type: =IFERROR(DATEVALUE(B2),B2). Drag down. This catches text dates like '02/22/2024' and converts them. For true dates, DATEVALUE returns the same serial number — no harm done.
3. Subtract — then handle blanks and errors. In E2, enter: =IF(OR(C2="",D2=""),"",C2-D2). This avoids #VALUE! when Payment Date is empty or 'TBD'. Note: C2 is Payment Date, D2 is cleaned Invoice Date.
4. Add conditional formatting to highlight delays. Select E2:E6 → Home → Conditional Formatting → Highlight Cell Rules → Greater Than → 14 → Light Red Fill.

Vendor	Cleaned Invoice Date	Payment Date	Days Between
Acme Corp	45349	45373	24
Nova Logistics	45362	45381	19
Skyline Tech	45339	TBD
Greenfield Labs	45338	45379	41
Orion Holdings	45370	45370	0

See how clean that is? No hidden functions. No version-specific bugs. Just arithmetic — Excel’s native language.

Going Further

You’ll often need more than just raw days. Here’s how to extend it:

• Business days only? Use =NETWORKDAYS.INTL(D2,C2,11) — the 11 excludes Sundays only (adjust code per your region).
• Count months, not days? =DATEDIF(D2,C2,"m") still works — but only if both dates are valid. Never rely on it for production reports.
• Calculate age in years? =INT((TODAY()-D2)/365.25) handles leap years better than YEARFRAC for most HR use cases.
• Dynamic cutoffs? To flag invoices unpaid after 30 days: =IF(C2="", "Pending", IF(C2-D2>30,"Overdue","OK")).

Surprising tip: If your dates include times (e.g., 2024-03-15 14:30), subtracting gives fractional days. Multiply by 24 to get hours: =(C2-D2)*24. You’ll see 36.5 — meaning 36 hours and 30 minutes.

When NOT to Use This

This subtraction method assumes both columns contain valid date serial numbers — or can be coerced into them. Avoid it when:

• Dates come from external systems with inconsistent formats (e.g., 15-Mar-2024 vs Mar 15, 2024 vs 20240315). Preprocess with TEXTSPLIT or Power Query first.
• You’re calculating duration across fiscal years where your company uses a 4-4-5 calendar. Excel’s date math doesn’t know your fiscal rules — build a lookup table instead.
• You need elapsed time down to the second. Serial numbers lose precision beyond ~10 decimal places. Use TEXT(C2-D2,"d\ hh:mm:ss") only for display — not calculation.
• Your workbook is shared with users on Excel 2007 or earlier. DATEVALUE fails on some regional settings. Test with =ISNUMBER(DATEVALUE("1/1/2024")) first.

Keyboard Shortcuts

Action	Shortcut	Notes
Open Format Cells	Ctrl + 1	Essential for checking date format
Insert Today’s Date	Ctrl + ;	Useful for dynamic ‘days since’ calcs
Toggle Formula View	Ctrl + `	Spot text dates instantly (they won’t recalculate)
Quick Fill	Ctrl + E	Convert messy date text before using DATEVALUE