TODAY & NOW
Return the current date (TODAY) or date and time (NOW); they update automatically.
Syntax
=TODAY() =NOW()=TODAY()Returns: Today's date
Recalculates each time the sheet opens.
=A2-TODAY()Returns: Days until the date in A2
Subtract dates to get a day count.
How TODAY & NOW evaluates
TODAY() returns the current date and NOW() returns the current date and time. Both take no arguments at all - the empty parentheses are part of the name, and typing =TODAY without them gives #NAME?, because Excel reads a bare word as a defined name. Both read the clock of the machine the file is open on, not a server, so a workbook opened in another time zone can legitimately show a different date to two people looking at the same file.
What they actually return is a number. Dates in Excel are serial numbers counting days from 1 January 1900, and TODAY() hands back a whole one while NOW() hands back a serial with a fractional part carrying the time. Excel normally applies a date format automatically when you type =TODAY() into an unformatted cell, which hides that detail until the day you subtract two dates and the answer appears as a date in 1900 instead of a number of days. The fix is always the same: set the result cell to General or Number, because the value was right all along.
The other thing to know is that both functions are volatile. A volatile function recalculates on every recalculation pass, not just when something it depends on changes - on file open, on any edit anywhere, on pressing F9 - and everything that refers to it recalculates too. That is what keeps an ageing report current, and it is also why a workbook with thousands of TODAY() calls feels slow and asks to be saved even when you changed nothing. Where a date should be captured rather than tracked, do not use TODAY() at all: Ctrl+; types today's date as a static value, and Ctrl+Shift+; types the current time.
- No arguments
- TODAY() and NOW() take none. Excel rejects =TODAY(A1) at entry with a message about too many arguments, and =TODAY without parentheses returns #NAME?.
- TODAY versus NOW
- TODAY() is a whole serial with no time part, so it equals midnight. NOW() carries the time as a fraction. =NOW()-TODAY() is therefore the time of day as a decimal fraction of a day.
- Volatility
- Both recalculate on every pass and mark the workbook as changed. A file containing them will prompt to save on close even if you only scrolled.
- Date arithmetic
- =A2-TODAY() gives whole days as a number. Because both sides are serials, no special function is needed - but the result cell often inherits a date format and must be set back to General.
- Comparisons
- =A2>TODAY() works directly and is the basis of overdue flags. Comparing against a text date such as "01/03/2024" does not - convert it with DATEVALUE or DATE first.
- Time zone and clock
- The value comes from the local machine's clock and regional settings. There is no UTC variant; a shared workbook has no single idea of today.
Errors TODAY & NOW returns
| Error | What it means and how to fix it |
|---|---|
| #NAME? | The parentheses are missing - =TODAY instead of =TODAY() - so Excel looked for a defined name called TODAY and found none. Add the empty parentheses. |
| #VALUE! | Date arithmetic against text, as in =TODAY()-A2 where A2 holds a date typed as text. Check with ISNUMBER(A2) and convert with DATEVALUE, or re-enter the column as real dates. |
| Too many arguments | Not an error value but a refusal at entry: Excel will not accept anything inside TODAY()'s parentheses. Anything you wanted to offset by belongs outside them, as in =TODAY()+30. |
| ##### | Also not an error - the column is too narrow to display a date, or the result is a negative serial, which Excel cannot show as a date. Widen the column, or format the cell as a number if the value is meant to be a day count. |
More examples
=IF(A2<TODAY(), "Overdue", "")Returns: A flag that appears on its own
Recalculates every time the file opens, so yesterday's due date is marked today without anyone touching the sheet. Wrap in IF(A2="", "", ...) so blank rows do not all read as overdue - an empty cell is treated as 0, which is well before today.
=EOMONTH(TODAY(), 0)Returns: The last day of the current month
The 0 means this month; -1 gives last month's end and 1 gives next month's. Useful as the upper bound of a month-to-date filter.
=NETWORKDAYS(TODAY(), A2)Returns: Working days remaining until a deadline
Counts weekdays only and excludes weekends. Pass a range of holiday dates as a third argument to exclude those too.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Both supported and volatile. The 1900 date system is the default; a workbook using the 1904 system offsets every serial by 1,462 days. |
| Excel 2019 | Identical behaviour. |
| Excel 2021 / Microsoft 365 | Identical. In Excel for the web the clock is the browser's, so the date follows the device rather than the file. |
| Google Sheets | Both supported. How often they refresh is a setting - File > Settings > Calculation offers on change, on change and every minute, or on change and every hour. |
| LibreOffice Calc | Both supported and volatile. They refresh only when AutoCalculate is on, otherwise on Ctrl+Shift+F9. |
Does TODAY & NOW Work in Google Sheets?
Yes. TODAY & NOW uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements TODAY() and NOW() with exactly zero arguments: passing anything between the parentheses returns #VALUE!, and writing TODAY without parentheses returns #NAME? because the editor does not support defined names.
Both read the browser's local clock. TODAY() builds a whole date serial from the local year, month and day, so it carries no time component, while NOW() adds the hours, minutes and seconds as the fractional part.
The editor treats TODAY, NOW, RAND, RANDBETWEEN and RANDARRAY as volatile: any formula whose text mentions one of them is marked for recalculation on every pass, regardless of what changed in the sheet.
The result is a plain number, and the editor renders it as a date only when the cell carries a date number format - a fresh cell shows the serial itself, which is the same value Excel would show if you set the cell to General.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
TODAY & NOW questions
Because TODAY() is not a stamp - it is a live reading of the clock, recalculated every time the file opens. It has no memory of the value it showed yesterday. For a date that must stay put, type it with Ctrl+; which enters a static value, or let a formula compute it once and then use Copy followed by Paste Special > Values to freeze the result.
The subtraction is correct; the cell format is wrong. Excel copies the date format from the cells in the expression, so a result of 30 is displayed as the thirtieth day of the serial calendar, 30 January 1900. Select the cell and set it to General or Number and the 30 appears.
Neither, if it is genuinely a timestamp. Both recalculate, so every row will show the same moment - the moment you last opened the file - rather than when each row was entered. Use Ctrl+Shift+; to type the time as a static value, or record the entry time with a macro. NOW() is for a live clock display, not for recording history.
It is a good suspect. Volatile functions force a recalculation on every pass and mark the file as modified, and everything that depends on them recalculates in turn - so a thousand rows each calling TODAY() is a thousand volatile cells. Put =TODAY() in one cell, name it, and have every other formula reference that cell instead. The dependency chain then runs from one volatile cell rather than a thousand.