THE EXCEL GUIDE Mastering Data & Spreadsheets

Home
Formulas and Functions Fundamentals

TODAY & NOW Functions + Excel Name Box: Step-by-Step

Learn how to work with dynamic date and time formulas.
By:

Why does your date formula show a different value every time you open the file, and is that a bug or working as intended? If you've typed =TODAY() into a cell and then panicked when the date changed overnight, you're not alone. That's the TODAY function doing exactly what it's supposed to do. By the end of this guide, you'll understand why it updates automatically, how to give that dynamic date cell a reusable name using the Excel Name Box, and how to reference that name cleanly across your entire workbook instead of retyping =TODAY() in a dozen different formulas.

Quick distinction before we get into steps: the TODAY function returns the current date only. The NOW function returns the current date and time. (Whether you need the seconds is between you and your deadline.) Both are dynamic and recalculate automatically. The only prerequisite is an open Microsoft Excel workbook.

Excel Name Box showing TodaysDate as a named range for a cell with the TODAY function
One named cell, referenced everywhere, that's the whole strategy.

Step 1: Enter a TODAY or NOW Formula Into the Cell You Want to Name

Click any empty cell — say, A1 on a dedicated reference sheet, or wherever makes sense in your workbook layout. Type =TODAY() and press Enter. The cell will immediately display today's date.

Here's the part most tutorials skip: that cell isn't storing a date. It's storing an instruction to calculate the current date every single time Excel recalculates the workbook. TODAY and NOW are what Excel calls volatile functions. They recalculate every time you make any change anywhere in the workbook, not just when the calendar date changes. Excel doesn't wait for midnight to update the value.

This is completely different from pressing Ctrl+;, which stamps a static date that never changes. If you need a permanent timestamp, use Ctrl+;. If you need a date that's always current, use =TODAY(). Mixing them up is an easy mistake, and it can ripple through an entire aging report before anyone notices.

If you're new to how formulas behave in general, the Excel Formulas and Functions for Beginners guide covers the underlying mechanics well before you get into date-specific functions.

Step 2: Use the Name Box to Give That Dynamic Date Cell a Reusable Name

Once your cell is showing today's date (or the current date and time, if you used =NOW()), you're ready to name it. This is the step that separates a formula buried in one cell from a workbook-wide resource you can call by name.

Type the Name and Press Enter — Not Click Away

Look to the left of the formula bar. That small input box displaying something like A1 is the Name Box. Click directly on it. The current cell address will highlight. Type your name — TodaysDate works well for a TODAY formula, NowTimestamp for a NOW formula — and press Enter.

If you type the name and then click somewhere else instead of pressing Enter, Excel quietly discards what you typed. The name is not saved. No error message. Nothing. It just disappears. Press Enter every time.

Rules That Make a Valid Named Range

Excel enforces a short list of naming rules:

  1. Names must start with a letter or underscore, not a number.
  2. No spaces are allowed. Use an underscore or camelCase instead, like Today_Date or TodaysDate.
  3. Names can't conflict with cell references like A1 or Z100.
  4. Keep names descriptive but short. ReportDate is better than D. TodaysDate is better than CurrentDateDynamicCell.

Step 3: Reference Your Named Dynamic Date Range Inside Other Formulas and Across Sheets

With TodaysDate defined, you can use it anywhere in the workbook — any cell, any sheet. Just type the name where you'd normally type a cell address.

Want to calculate how many days a project is overdue? Instead of =A1-C5 (which tells the next person who opens the file absolutely nothing), you can write =TodaysDate-ProjectStart. Readable. Auditable. Self-documenting. That's the real value of pairing the Name Box with dynamic date functions: the formula explains itself.

Named ranges defined this way are workbook-scoped by default, meaning TodaysDate resolves correctly whether you reference it from Sheet1, Sheet3, or a summary tab added later. Excel looks up the name, finds the cell, recalculates the volatile formula, and returns the current date every time.

Understanding how cell addresses and named ranges interact is worth a closer look if this is new territory — Understanding Cell References in Excel fills in the gaps.

Common Mistakes When Using TODAY and NOW with the Excel Name Box

Three problems come up repeatedly, and all three are fixable in under ten seconds once you know what's happening.

You clicked away from the Name Box instead of pressing Enter

The name wasn't saved. Click the Name Box again, retype the name, and press Enter this time. Excel gives no error or warning when this happens — the input just disappears.

You named the cell before entering the formula

The named range now points to an empty cell. Any formula referencing TodaysDate will return zero or a blank. Fix it by entering =TODAY() into that cell — the name already points there, so no re-naming is needed.

You expected NOW to tick like a clock

It doesn't. NOW recalculates on every worksheet action — a keystroke, a data entry, a formula change — but it won't update while you're just watching the screen. If you need the timestamp to refresh, press F9 to force recalculation manually. That's not a flaw in NOW; it's how Excel's calculation engine works across all volatile functions.

You're using TODAY or NOW in many cells and your workbook is slow

If your workbook has tens of thousands of rows and heavy formula use, stacking volatile functions across many cells will slow calculation noticeably. Use a single named source cell — like the TodaysDate approach this article covers — and reference that name everywhere. One volatile calculation instead of fifty.

If you take one thing from this article: TODAY and NOW update automatically because they're volatile functions, and that's a feature, not a bug. Name one source cell through the Name Box, reference that name everywhere, and you'll never have a stale date in a report again.

Frequently Asked Questions

Why does my Name Box not save the name I typed?

You most likely clicked away from the Name Box instead of pressing Enter. Excel requires you to press Enter to confirm the name — clicking elsewhere cancels the input without saving. Click the Name Box again, retype the name, and press Enter to save it.

What is the difference between TODAY and NOW in Excel?

The TODAY function returns the current date with no time component. The NOW function returns the current date and time, including hours and minutes. Both are dynamic and recalculate automatically whenever the workbook recalculates. Use TODAY for date-only tracking like deadlines or aging reports, and NOW when you need a time-stamped record.

Does a named range update automatically when it contains TODAY or NOW?

Yes. The named range is just a label pointing to the cell — the cell still holds a volatile formula that recalculates on every workbook action. Any formula referencing that named range will automatically pick up the recalculated value. You don't need to update the name itself.