Yes, =TODAY()-A2 calculates how many days between today and a date in A2. But if that date is in the future—or buried in a messy dataset with blanks, text, or holidays—you’ll get negative numbers, #VALUE! errors, or silently wrong totals.
The Problem
You’re tracking project timelines across 9 departments. Finance says Q2 rollout starts April 12—but Marketing entered "4/12/24" as text, HR pasted a date from Outlook with extra spaces, and Legal used a custom format that Excel won’t recognize as a date. When you try =TODAY()-B2 on row after row, you get: #VALUE!, -156, 0, and 37. No one knows which number is trustworthy.
| Project | Start Date | Formula Attempt (C2:C10) | Result |
|---|---|---|---|
| Sunrise CRM | 2024-04-12 | =TODAY()-B2 | 42 |
| Nexus Payroll | "4/12/24" | =TODAY()-B3 | #VALUE! |
| Vega Compliance | 2024-05-20 | =TODAY()-B4 | -156 |
| Orion Onboarding | "2024-03-15" | =TODAY()-B5 | #VALUE! |
| Aurora Docs | 2024-06-30 | =TODAY()-B6 | -22 |
| Helix Training | =TODAY()-B7 | 210 | |
| Lyra Security | 2024-03-01 | =TODAY()-B8 | 70 |
| Terra Audit | 45220 | =TODAY()-B9 | 12 |
| Quill Reporting | 2024-04-12 | =TODAY()-B10 | 42 |
Notice row 7? Blank cell B7 returns 210—because Excel treats empty cells as zero, and zero equals January 0, 1900. That’s 210 days ago. Not what anyone intended.
The Solution
Use this 4-step pattern. It’s not flashy—but it catches every trap above:
- In cell C2, enter:
=IF(B2="", "", IF(ISNUMBER(B2), TODAY()-B2, "Invalid date")) - Select C2, then press Ctrl+C.
- Select C3:C10, then press Ctrl+V.
- Press Alt+H+O+I to auto-fit column C.
That’s it. The formula checks for blanks first, then validates numeric date serials before subtracting. Text like "4/12/24" fails ISNUMBER(), so it returns "Invalid date" instead of crashing. And yes—it correctly handles Excel’s internal date serials like 45220 (which equals 2023-10-20).
| Project | Start Date | Clean Formula (C2:C10) | Result |
|---|---|---|---|
| Sunrise CRM | 2024-04-12 | =IF(B2="", "", IF(ISNUMBER(B2), TODAY()-B2, "Invalid date")) | 42 |
| Nexus Payroll | "4/12/24" | =IF(B3="", "", IF(ISNUMBER(B3), TODAY()-B3, "Invalid date")) | Invalid date |
| Vega Compliance | 2024-05-20 | =IF(B4="", "", IF(ISNUMBER(B4), TODAY()-B4, "Invalid date")) | -156 |
| Orion Onboarding | "2024-03-15" | =IF(B5="", "", IF(ISNUMBER(B5), TODAY()-B5, "Invalid date")) | Invalid date |
| Aurora Docs | 2024-06-30 | =IF(B6="", "", IF(ISNUMBER(B6), TODAY()-B6, "Invalid date")) | -22 |
| Helix Training | =IF(B7="", "", IF(ISNUMBER(B7), TODAY()-B7, "Invalid date")) | ||
| Lyra Security | 2024-03-01 | =IF(B8="", "", IF(ISNUMBER(B8), TODAY()-B8, "Invalid date")) | 70 |
| Terra Audit | 45220 | =IF(B9="", "", IF(ISNUMBER(B9), TODAY()-B9, "Invalid date")) | 12 |
| Quill Reporting | 2024-04-12 | =IF(B10="", "", IF(ISNUMBER(B10), TODAY()-B10, "Invalid date")) | 42 |
The beauty of this approach is that it doesn’t try to fix bad input—it flags it. You see exactly where cleaning is needed. And because ISNUMBER() accepts true dates *and* valid date serials, it works whether your source is formatted as "2024-04-12", 45402, or even =DATE(2024,4,12).
Going Further
You’ll want variations depending on context. Here are four you’ll actually use:
- Business days only? Replace
TODAY()-B2withNETWORKDAYS(B2,TODAY()). Just remember: NETWORKDAYS excludes weekends *and* treats start/end dates inclusively—so if today is April 12 and B2 is also April 12, it returns 1, not 0. - Exclude holidays? Add a holiday range:
NETWORKDAYS(B2,TODAY(),Holidays!A2:A15). List holidays in column A of a sheet named Holidays. - Count days since last update? Use
=IF(B2="", "", TODAY()-B2)— but wrap it in conditional formatting: select C2:C10 → Alt+H+L → New Rule → “Format only cells that contain” → “Cell Value” → “less than” → “30” → green fill. Now overdue items jump out. - Calculate age in days (not years)? Use
=DATEDIF(B2,TODAY(),"d"). Warning: DATEDIF is undocumented, unreliable with leap years, and breaks if B2 > TODAY(). So test it first—then fall back to=TODAY()-B2for consistency.
Here’s the counterintuitive tip: Never use DATEDIF for “how many days” unless you’ve verified both dates are valid and in chronological order. Its “d” argument sometimes miscounts by one day around February 29. Real-world example: =DATEDIF("2023-02-28","2024-02-28","d") returns 365—not 366—even though 2024 is a leap year. The simple subtraction gives 366. Always.
When NOT to Use This
This formula assumes your goal is “days between now and a point in time.” It fails—or misleads—when:
- You need elapsed time across months or years with variable lengths (use DATEDIF with "ym" or "md" only after validating inputs).
- Your data includes timestamps (e.g., "2024-04-12 14:30"). TODAY() has no time component, so =TODAY()-B2 drops hours/minutes. Fix: use =INT(TODAY()-B2) to truncate time, or =TODAY()-INT(B2) to keep time math intact.
- You’re comparing two historical dates (not “today”). Then replace TODAY() with the later date—but verify it’s truly later. A silent swap causes massive negatives.
- You’re auditing financial close dates. Accounting teams often need *calendar days excluding weekends AND statutory holidays*. That requires NETWORKDAYS with a validated holiday list—not arithmetic subtraction.
If your dataset has more than 10% invalid or ambiguous dates, stop calculating. Clean first. Use Data → Text to Columns → Delimited → Space → Finish on columns with trailing spaces. Then apply =DATEVALUE(TRIM(B2)) to convert text dates. Only then run the day-count formula.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Auto-fit column width | Alt+H+O+I | Works on selected column(s); fastest way to preview results |
| Open Format Cells dialog | Ctrl+1 | Crucial for checking if a cell is truly a date (look for Type = "Date") |
| Toggle formula view | Ctrl+` (backtick) | See all formulas at once—spot hidden text dates instantly |
| Paste values only | Alt+E+S+V+Enter | After fixing dates, paste values to lock results and prevent recalculation drift |
