Home › Excel functions › TODAY & NOW

TODAY and NOW in Excel: Current Date and Time

TODAY returns the current date and NOW the current date and time. Both update automatically, which makes them perfect for countdowns, ages and overdue warnings.

Excel CSE · 2 min read
In this guide
  1. Syntax
  2. How dates work
  3. Practical examples
  4. A date that doesn't change
  5. When they update
  6. Highlight overdue items
  7. FAQ

Syntax

=TODAY() =NOW()

Neither takes arguments, but the empty brackets are required. The result is formatted as a date (TODAY) or date and time (NOW); change the look with Ctrl+1.

How Excel stores dates

Excel stores a date as a number: the count of days since January 1, 1900. Times are fractions of a day: 12:00 noon is 0.5. That's why you can simply subtract dates to get the number of days between them, and add 7 to get the date a week later.

Practical examples

GoalFormula
Days until a deadline in A2=A2-TODAY() (format as a number)
Days since an invoice date=TODAY()-A2
Overdue?=IF(A2<TODAY(), "Overdue", "")
Due within 7 days?=AND(A2>=TODAY(), A2<=TODAY()+7)
Age in years=DATEDIF(A2, TODAY(), "Y")
Date a week from now=TODAY()+7
Last day of this month=EOMONTH(TODAY(), 0)
Hours since a timestamp=(NOW()-A2)*24

More on ages in DATEDIF and on month ends in EOMONTH. For workdays only, see NETWORKDAYS.

A date that doesn't change: shortcuts

TODAY changes every day, so it's wrong for things like "order date" that must stay fixed. Use a keyboard shortcut instead; it types the current value as a fixed entry:

  • Ctrl+; inserts today's date.
  • Ctrl+Shift+; inserts the current time.
  • Both after each other (with a space in between) give a fixed date and time.

When do TODAY and NOW update?

They're "volatile": they recalculate every time the workbook recalculates, for example when you open it or change any cell. They don't tick live like a clock. Press F9 to force an update. In very large workbooks, many volatile formulas can slow things down; one TODAY() in a cell that other formulas refer to is better than hundreds.

Highlight overdue items

Select the date column and add a conditional formatting rule with the formula =A2<TODAY(), or use Highlight Cells Rules › A Date Occurring for options like "Yesterday" or "Next week".

Frequently asked questions

Why does my TODAY formula show a number like 46338?

The cell is formatted as a number. Format it as a date with Ctrl+1 › Date.

How do I stop TODAY from changing?

Copy the cell and use Paste Special › Values, or enter the date with Ctrl+; in the first place.

What's the difference between TODAY and NOW?

TODAY returns only the date. NOW returns the date and the current time.