Yes, =TODAY() returns today’s date in Excel. But if you paste that result into a report header and forget it updates every time the file opens, your client thinks their contract renewal is due *today* — even though it’s actually due June 12, 2025.
The Problem
You’re finalizing Q2 sales summaries for Alibaba’s regional partners. Marketing sends you a raw export from their CRM: unformatted dates, inconsistent labels, and no ‘as-of’ timestamp. You slap =TODAY() into cell A1 to mark the report date — then copy-paste values into the dashboard. Two weeks later, Finance flags discrepancies: the ‘Report Date’ column shows July 18, but the underlying data only covers up to June 30.
Here’s what the messy version looks like — pulled straight from last week’s real audit:
| Partner | Last Sale Date | Report Date (A1) | Days Since Sale |
|---|---|---|---|
| Acme Corp | 2024-05-22 | =TODAY() | =A3-B3 |
| Nexus Labs | 2024-06-14 | =TODAY() | =A4-B4 |
| Stellar Dynamics | 2024-06-30 | =TODAY() | =A5-B5 |
| Vega Solutions | 2024-04-11 | =TODAY() | =A6-B6 |
| Orion Trading | 2024-06-25 | =TODAY() | =A7-B7 |
| Zenith Group | 2024-05-08 | =TODAY() | =A8-B8 |
The issue isn’t the formula itself. It’s that TODAY() recalculates on every recalculation — including when someone opens the file on a different day. That means your ‘Days Since Sale’ column shifts silently. Vega Solutions jumps from 65 days to 67 overnight. And if this sheet gets emailed or archived, the date becomes a moving target.
The Solution
Fix it in three precise steps — no macros, no add-ins, just native Excel logic.
- Type =TODAY() into cell D1 — yes, that’s correct. But don’t leave it there. Immediately press Ctrl+C, then right-click cell D1 → Paste Special → Values (Alt+E+S+V → Enter). This freezes the date as a static value: 2024-07-04 (or whatever today is). Now it won’t budge.
- In column D, label it “As-of Date” — not “Report Date”. Why? Because “report date” implies when the report was generated. “As-of date” signals the cutoff for data inclusion. Then format D1 as
yyyy-mm-dd(right-click → Format Cells → Number → Custom). - Recalculate Days Since Sale using D1 instead of TODAY(): In E3, enter
=D$1-B3. Lock the row with$1so it stays fixed when dragging down. Drag from E3 to E8.
That’s it. No volatility. No surprises. Here’s how it looks after the fix:
| Partner | Last Sale Date | As-of Date (D1) | Days Since Sale |
|---|---|---|---|
| Acme Corp | 2024-05-22 | 2024-07-04 | 43 |
| Nexus Labs | 2024-06-14 | 2024-07-04 | 20 |
| Stellar Dynamics | 2024-06-30 | 2024-07-04 | 4 |
| Vega Solutions | 2024-04-11 | 2024-07-04 | 84 |
| Orion Trading | 2024-06-25 | 2024-07-04 | 9 |
| Zenith Group | 2024-05-08 | 2024-07-04 | 57 |
Notice how all Days Since Sale values now anchor to one immutable reference point. That’s what makes audits clean and stakeholders confident.
Going Further
TODAY() shines when combined — but only when you control its volatility.
Highlight upcoming deadlines: In column F, use conditional formatting with this rule: =AND(B3>=TODAY(),B3<=TODAY()+14). Applies yellow fill to any sale date within the next two weeks. Works because TODAY() here is *intentionally* volatile — you want it to update daily in the dashboard view.
Calculate business days left until contract renewal: If contracts renew on fixed dates in column C (e.g., C3 = 2025-06-12), use =NETWORKDAYS(TODAY(),C3) in D3. This *needs* TODAY() to stay live — otherwise you’d miscount weekends and holidays.
Create dynamic headers: Type this in cell A1: ="Sales Summary as of "&TEXT(TODAY(),"dddd, mmmm dd, yyyy"). Returns “Sales Summary as of Thursday, July 04, 2024”. Just remember — if you email this sheet, the header will change for the recipient unless you convert to values first.
Here’s a counterintuitive tip: Never nest TODAY() inside IFERROR(). Try =IFERROR(TODAY()/0,TODAY()). It looks clever — “if error, return today.” But Excel evaluates both branches during recalc. So TODAY() runs twice, increasing volatility unnecessarily. Instead, use =TODAY() alone and handle errors upstream.
When NOT to Use This
TODAY() has hard limits — and some are non-obvious.
- Archived reports: If you save a file as PDF or send it externally, TODAY() becomes useless noise. Convert to values first — or better, stamp with Ctrl+; (semicolon) for static date entry.
- Shared workbooks with manual calculation: If someone toggles Formulas → Calculation Options → Manual, TODAY() stops updating — but doesn’t warn you. The date freezes silently. Avoid TODAY() entirely in shared, manually-calculated files.
- Historical analysis across years: Don’t use =TODAY()-365 to get “last year’s date.” It fails on leap years. Use
=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))instead — accurate, safe, and handles Feb 29 cleanly. - Time-sensitive compliance logs: Regulators require immutable timestamps. TODAY() violates that. Use Ctrl+; + Ctrl+Shift+: for date+time stamp — then paste as values immediately.
Also: TODAY() returns the system date — not UTC. If your team spans APAC and EMEA, and you’re logging events globally, TODAY() will show different dates depending on who opens the file. Use =NOW() + timezone adjustment only if you’ve built a robust offset table — otherwise, stick to static stamps.
Keyboard Shortcuts
These save seconds — and prevent accidental volatility:
| Action | Shortcut | Use Case |
|---|---|---|
| Insert static today’s date | Ctrl+; | Fastest way to log a fixed date (e.g., invoice issued) |
| Insert static current time | Ctrl+Shift+: | Pair with Ctrl+; for full timestamp (e.g., QA sign-off) |
| Paste values only | Alt+E → S → V → Enter | Freeze TODAY() or other formulas instantly |
| Toggle formula view | Ctrl+` (grave accent) | Spot hidden TODAY() calls in large workbooks before sending |
