TODAY and NOW in Excel: Dates That Update Themselves
Use TODAY and NOW in Excel for days until a deadline, overdue flags, ages and timestamps. How dates work as numbers, and how to stop them updating.
By Ayoub Farid · · 1 min read
TODAY returns today's date and NOW returns the date and time. Both update every time the sheet recalculates, which makes them perfect for countdowns, overdue flags and ages.
Syntax
=TODAY()
=NOW()No arguments, but the brackets are required.
Dates are numbers
Excel stores dates as numbers of days (1 January 1900 is day 1). That's why you can subtract them.
Days until a deadline
=C2-TODAY()Format the cell as a number, not a date. Negative means it's past.
Overdue flag
=IF(C2<TODAY(),"Overdue","")Count overdue items:
=COUNTIF(C2:C200,"<"&TODAY())Age in years
=DATEDIF(B2,TODAY(),"y")See DATEDIF and calculate age.
Start and end of this month
=EOMONTH(TODAY(),-1)+1 first day of this month
=EOMONTH(TODAY(),0) last day of this monthHours between two times
=(NOW()-B2)*24Because one day = 1, multiply by 24 for hours. See time difference.
Stop it from updating (a fixed date stamp)
TODAY changes every day. For a fixed date (when a row was entered), use a shortcut instead:
- Ctrl + ; inserts today's date as a fixed value
- Ctrl + Shift + ; inserts the current time
On Mac Excel, Command + ; and Command + Shift + ; work too.
Frequently asked questions
How do I insert today's date in Excel?
=TODAY() for a date that updates, or Ctrl+; for a fixed date.
How do I calculate days between today and a date?
=A2-TODAY() (days until) or =TODAY()-A2 (days since).
Why does my TODAY formula show a number?
The cell is formatted as a number. Change it to a date format.
Does TODAY work in Google Sheets?
Yes. Ctrl+; also inserts a fixed date in Google Sheets.