Why does your invoice template show yesterday’s date after reopening? Why did your ‘Days Since Last Contact’ column freeze on March 12? Why does =TODAY() work fine in cell A1 but return #VALUE! when nested inside TEXT() without parentheses?
Quick Answer
Type =TODAY() into any empty cell (e.g., B2) and press Enter — that’s it. No arguments, no quotes, no extra keystrokes. Excel inserts today’s date as a serial number (like 45362 for 2024-03-15) and formats it as a date automatically if the cell isn’t pre-formatted as Text.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Direct formula entry | Click cell → type =TODAY() → Enter | One-off dates, dashboards, headers | Updates every time sheet recalculates (F9) |
| Keyboard shortcut + formula | Select cell → press Ctrl + ; (semicolon) → then edit to =TODAY() if needed | Static date entry (Ctrl+; gives static), then convert to dynamic | Ctrl+; inserts static date — not live — so you must manually replace |
| With TEXT formatting | =TEXT(TODAY(),"dd-mm-yyyy") | Reports needing consistent display (e.g., PDF exports) | Returns text — can’t be used in date math unless wrapped in DATEVALUE() |
| Combined with IF for conditional logic | =IF(A2="Pending",TODAY(),"") | Status-triggered timestamps (e.g., “Started On” only when status changes) | Doesn’t auto-update if A2 changes back — requires manual recalc or volatile helper |
| Using Alt+M, V, T (Formula Auditing) | Alt+M → V → T opens Formula Auditing toolbar, then type =TODAY() manually | Users who rely on ribbon navigation over typing | No real advantage — longer than typing directly |
| Named range with TODAY() | Formulas → Define Name → Name: ReportDate → Refers to: =TODAY() | Workbooks with dozens of TODAY() references (centralized control) | Can’t be used in data validation or conditional formatting formulas |
Method 1 Deep Dive
Let’s say you’re building a supplier follow-up tracker. Column A holds vendor names, B has last contact date, C should show ‘Days Since Contact’. You want this to update daily — no manual edits.
In cell C2, enter =TODAY()-B2. If B2 contains 2024-03-08, and today is 2024-03-15, C2 returns 7. That’s clean. But here’s what most miss: if B2 is blank, TODAY()-B2 returns 45362 — Excel’s serial number for today, because subtracting zero from a date gives the date itself as a number.
The fix? Wrap it: =IF(B2="", "", TODAY()-B2). Now C2 stays empty until a date appears in B2. Try it in your sheet now — type that into C2, then copy down to C10. Your sample data might look like this:
| A2:A7 | B2:B7 | C2:C7 (formula) |
|---|---|---|
| AlphaTech Ltd | 2024-03-10 | 5 |
| Nexus Logistics | 2024-02-28 | 15 |
| Veridian Systems | 2024-03-15 | 0 |
| Stellar Dynamics | ||
| Orion Labs | 2024-01-22 | 53 |
| Cedar Ridge Inc | 2024-03-12 | 3 |
The beauty of this approach is that it’s lightweight — no macros, no add-ins. And if you later insert a new row between A5 and A6, Excel auto-updates C6:C7 because the formula range shifts cleanly.
Method 2 Deep Dive
Now imagine you’re drafting a contract renewal dashboard. You need a header that says “Report generated on Friday, March 15, 2024” — and it must stay formatted *exactly* like that, even after export to PDF.
You could use ="Report generated on "&TEXT(TODAY(),"dddd, mmmm dd, yyyy") in cell F1. That works — but here’s the counterintuitive part: if your regional settings use comma as decimal separator (e.g., France, Germany), TEXT() may break unless you force English formatting with TEXT(TODAY(),"[$-en-US]dddd, mmmm dd, yyyy").
Try it: In F1, paste ="Report generated on "&TEXT(TODAY(),"[$-en-US]dddd, mmmm dd, yyyy"). It returns “Report generated on Friday, March 15, 2024” — reliably, regardless of system locale. What makes this elegant is that it sidesteps Windows regional overrides entirely. Bonus tip: To force uppercase day names, wrap in UPPER(): =UPPER(TEXT(TODAY(),"[$-en-US]dddd")).
This matters most when sharing files across APAC/EMEA teams. Sarah Chen in Singapore and Klaus Weber in Berlin both see “FRIDAY, MARCH 15, 2024” — no translation, no formatting surprises.
Cheat Sheet
| Action | Shortcut / Formula | Notes |
|---|---|---|
| Insert live today’s date | =TODAY() | Updates on open & recalc (F9); never updates in background |
| Insert static today’s date | Ctrl + ; | One-time stamp — won’t change tomorrow |
| Today + 7 days | =TODAY()+7 | Works with any integer — negative numbers go backward |
| First day of current month | =DATE(YEAR(TODAY()),MONTH(TODAY()),1) | Useful for monthly reporting ranges |
| Today formatted as “15-Mar-2024” | =TEXT(TODAY(),"dd-mmm-yyyy") | Result is text — not usable in date math |
| Prevent accidental overwrite | Protect sheet → allow only specific cells to be edited | Stop colleagues from deleting =TODAY() in critical headers |
| Check if TODAY() is working | Press F9 — watch date change instantly | If it doesn’t update, check Formulas → Calculation Options → set to Automatic |
