Insert the Current Date: TODAY and NOW
Almost everyone who lands here wants a date typed into a cell, and a function is the wrong instrument for that job. Ctrl+; writes a constant that will still be correct next month; the function writes a formula whose only honest reading is "the last time this file was open". The two look identical on the day they are entered and disagree from the following morning onwards, which is the single mistake behind most of the questions below. Keep the functions for figures that genuinely have to track the calendar — a days-remaining column, an ageing report, a header that dates itself — and the shortcuts for anything recording that something happened.
The formula
=TODAY() the date only, no time; re-evaluates every day
=NOW() date and time in one serial; re-evaluates on every edit
Ctrl+; types a STATIC date — a constant, not a formula
Ctrl+Shift+; types a STATIC time
Ctrl+; SPACE Ctrl+Shift+; both, statically, in one cell
=TODAY()+30 a due date 30 days out that moves with the calendar
=B2-TODAY() days remaining until the deadline in B2A worked example
C2 has to record the day an order was checked, and it must still say that day when someone reopens the file next week.
Select C2 and press Ctrl+; — not =TODAY()
C2 holds the constant 14 August 2026 and still reads 14 August 2026 a week later. Type =TODAY() into the same cell instead and by next Friday it reads 21 August 2026: the formula re-evaluates when the workbook opens, so the only thing it can ever tell you is when you last looked at it.
Which one do I need?
| If you want to… | Use |
|---|---|
| A date that should genuinely change every day — a report header, a days-remaining column | =TODAY(). It re-evaluates on open and on every edit, which is the point. |
| A value that records when something happened and must never move | Ctrl+; for the date, Ctrl+Shift+; for the time, or Ctrl+; then a space then Ctrl+Shift+; for both in one cell |
| You need the clock time as well as the date | =NOW(). One serial carries both, but a cell formatted as Short Date hides the time half — set a custom format such as dd mmm yyyy hh:mm to see it. |
| A date should stamp itself the moment another cell is filled in | Excel has no trigger for this. Enable File > Options > Formulas > Iterative calculation and put the self-referencing =IF(A2="","",IF(B2="",NOW(),B2)) in B2, or write a Worksheet_Change macro. Both are fragile; Ctrl+; is usually the honest answer. |
| The date is correct but displays wrongly — wrong order, unwanted time, US format | That is a number-format question, not a function question |
| Filling a run of consecutive dates down a column | Type the first date, then drag the fill handle — Excel increments by one day, and the Auto Fill Options button offers weekdays or months instead |
| You're in Google Sheets | TODAY() and NOW() behave identically and Ctrl+; / Ctrl+Shift+; insert the same static values. Sheets adds Ctrl+Alt+Shift+; for date and time together, which Excel has no single shortcut for. |
Frequently asked questions
How do I stop a TODAY() cell from changing?
Convert it to its result. Either copy the cell and use Paste Special > Values over the top of it, or click into the formula bar, press F9 to replace the formula with the date it currently evaluates to, and press Enter. Both leave a constant behind that no recalculation can touch.
Does NOW() tick like a clock?
No. It updates only when the sheet recalculates — an edit anywhere in the workbook, opening the file, or pressing F9. Left alone on screen it shows the same minute indefinitely, which surprises people who expected a live clock and is why NOW() makes a poor stopwatch.
Do TODAY and NOW slow down a large workbook?
They can. A volatile function marks itself and everything downstream of it as needing recalculation on every single change, so thousands of TODAY-driven formulas mean thousands of chains re-running each time you press Enter. The fix is to put =TODAY() in one cell and point every other formula at that cell, leaving exactly one volatile cell in the workbook.
What's the difference between Ctrl+; and =TODAY() if both show today's date?
Only one of them is still true tomorrow. Ctrl+; types a constant — the date is entered once and stored as a value. =TODAY() stores a formula that produces a date on demand, so the cell shows whatever today happens to be each time the workbook is calculated. They look identical on the day you enter them and diverge from then on.
New guides and tools, once a month
DE + EN · double opt-in · no spam