Excel TODAY and NOW Functions
last modified October 5, 2026
The TODAY and NOW functions are the two ways Excel reports
the current moment. TODAY returns today's date, while
NOW returns the current date together with the current time. Both
take no arguments, and both are volatile, which means they refresh every time
the workbook recalculates. This tutorial covers their syntax, the serial-number
system behind them, practical calculations, formatting, and how to freeze their
values when you need a fixed record.
TODAY and NOW Function Basics
The TODAY function returns the serial number of the current date: a
whole number with no time part. The NOW function returns the serial
number of the current date and time, so it carries a fractional part that
represents the time of day. Neither function accepts arguments.
| Component | TODAY | NOW |
|---|---|---|
| Function Name | TODAY | NOW |
| Syntax | =TODAY() | =NOW() |
| Arguments | None | None |
| Return Value | Current date serial (integer, time 0) | Current date and time serial (with fraction) |
| Volatile | Yes | Yes |
This table compares the two functions. TODAY is effectively
NOW truncated to midnight, so =NOW()-TODAY() returns
the fraction of the day that has elapsed. Both functions are volatile: they
update automatically whenever the worksheet recalculates, including each time
the workbook is opened. Neither function takes an argument, so you always write
them with empty parentheses.
Serial Numbers Behind TODAY and NOW
Excel does not store a date as text. It stores a serial number that counts days
from a fixed starting point. In the default 1900 date system, January 1, 1900 is
serial number 1, and each following day adds one. A date is the integer part of
the serial; a time is the decimal part, where 0.5 is exactly noon.
TODAY returns only the integer, while NOW returns the
integer plus the day's fraction.
| Value | Serial number |
|---|---|
| January 1, 2025 | 45658 |
| October 5, 2026 | 46300 |
| 12:00 noon | 0.5 |
The serial for TODAY changes every day, so a cell that shows 46300
today shows 46301 tomorrow. When you need a fixed serial for testing, use
DATE instead: =DATE(2026,10,5) always returns 46300.
The relationship between the two functions is easy to check with
=NOW()-TODAY(), which yields the current time as a fraction between
0 and 1.
=DATE(2026, 10, 5) ' 46300, a fixed serial =NOW()-TODAY() ' current time as a fraction, e.g. 0.5
Volatile Functions and Static Timestamps
Because TODAY and NOW are volatile, their results
change. Excel recalculates them when the worksheet is recalculated, when the
workbook is opened, and whenever a dependent cell changes. NOW is
not a running clock: it advances only at recalculation, so the seconds do not
tick continuously in the cell.
To capture a value that never changes, either type it directly or convert the
formula to a value. Use Ctrl+; to insert the current date and
Ctrl+Shift+; to insert the current time. To insert both, press
Ctrl+;, type a space, then press Ctrl+Shift+;. You can
also select a formula result, copy it, and use Paste Special > Values to
replace the formula with its current value.
| Shortcut | Inserts |
|---|---|
| Ctrl+; | Current date (static) |
| Ctrl+Shift+; | Current time (static) |
| Ctrl+; then Space then Ctrl+Shift+; | Current date and time (static) |
The static shortcuts write a literal value into the cell, so the result no
longer updates. In contrast, =TODAY() and =NOW()
always recalculate. If a date or time seems frozen and refuses to update, check
File > Options > Formulas and make sure Workbook Calculation is set to
Automatic.
Basic TODAY Example
This example demonstrates the simplest use of the TODAY function to display the current date.
=TODAY()
This formula returns the current date as a serial number, and Excel applies a date format automatically when the cell was General. On October 5, 2026 the cell displays 10/5/2026; the next day it displays 10/6/2026. The real value is not constant, and the exact display depends on your regional settings.
Basic NOW Example
This example shows the simplest use of the NOW function to display the current date and time.
=NOW()
This formula returns the current date and time as a serial number with a fractional part. At 2:30 PM on October 5, 2026 the cell displays 10/5/2026 14:30, and the value refreshes on the next recalculation rather than continuously.
TODAY and NOW Arithmetic
Because dates and times are ordinary numbers, you can add and subtract them
directly. Adding a whole number to TODAY moves forward by that many
days, and the difference between NOW and TODAY is the
time of day.
=TODAY()+1 ' tomorrow's date =TODAY()-7 ' one week ago =NOW()-TODAY() ' the time of day as a fraction =TODAY()-DATE(2023,1,1) ' days elapsed since January 1, 2023
If today is October 5, 2026 (serial 46300), then =TODAY()+1 is
October 6, 2026, and =TODAY()-DATE(2023,1,1) is 46300 minus 44927,
or 1373 days. These results change as the date advances, which is exactly what
makes the functions useful.
Calculating Days Until Deadline
You can use TODAY to calculate the days remaining until a deadline or event. This example shows a simple countdown.
| A | B |
|---|---|
| Project Deadline | 12/31/2026 |
| Days Remaining | =B1-TODAY() |
The table stores a deadline in B1 and calculates the days remaining in B2 by
subtracting TODAY() from the deadline. The result decreases by one
each day and becomes negative once the deadline has passed.
=B1-TODAY()
The subtraction returns a plain number of days, so format B2 as a number rather than a date. On October 5, 2026 the deadline above yields 87, because December 31, 2026 is serial 46387. A negative result means the deadline is already in the past.
Calculating Age from Birthdate
TODAY is commonly used to calculate age from a birthdate. This example shows how to compute age in completed years.
| A | B |
|---|---|
| Birthdate | 5/12/1980 |
| Current Age | =DATEDIF(B1,TODAY(),"Y") |
The table contains a birthdate in B1 and calculates the completed years in B2
with DATEDIF. This is more accurate than dividing by 365 because it
accounts for leap years and for whether the birthday has already occurred this
year.
=DATEDIF(B1, TODAY(), "Y") ' exact completed years =INT((TODAY()-B1)/365) ' quick approximation
For a birthdate of May 12, 1980, both formulas return 46 on October 5, 2026. The
quick version divides the day difference by 365 and truncates with
INT; over long periods it can drift by a year because it ignores
leap days. Use DATEDIF when the exact age matters.
Timestamp Creation with NOW
NOW is often used to stamp a worksheet with the moment it was last edited. This example shows a conditional timestamp.
| A | B |
|---|---|
| Order received | Y |
| Logged at | =IF(B1="","",NOW()) |
The formula displays the current time only when B1 is not empty. It does not,
however, freeze the value: because NOW is volatile, B2 keeps
updating on every recalculation even when B1 is unchanged. The IF
controls whether a timestamp appears, not when it changes.
=IF(B1="","",NOW())
To create a genuinely static timestamp, press Ctrl+Shift+; at the
moment of the event, or copy the formula result and paste it back with Paste
Special > Values. Automating the capture requires a small VBA
Worksheet_Change procedure, because no worksheet formula can stop the clock on
its own.
Calculating Time Elapsed
NOW can replace an end time in a duration calculation, or you can subtract two fixed times. This example measures hours worked.
| A | B |
|---|---|
| Start Time | 9:00 AM |
| End Time | 5:30 PM |
| Hours Worked | =(B2-B1)*24 |
The table shows start and end times with a duration calculation. Subtracting the times gives a fraction of a day, and multiplying by 24 converts it to hours. In this example 5:30 PM minus 9:00 AM is 8.5 hours.
=(B2-B1)*24
The formula returns 8.5. If you want the result shown as 8:30 rather than as
decimal hours, drop the *24 and format the cell as a time. You can
replace B2 with NOW() to track elapsed time against the current
moment.
Creating Dynamic Headers
TODAY and NOW are useful in report headers to show when the data was current. This example demonstrates a dynamic header.
="Sales Report as of "&TEXT(TODAY(),"mmmm d, yyyy")
This formula builds a text string that combines a label with the current date.
On October 5, 2026 it returns "Sales Report as of October 5, 2026". The
TEXT function is essential here: concatenating a date directly
would insert its serial number instead of a readable date.
Date-Based Conditional Formatting
TODAY can drive conditional formatting rules. This example highlights overdue tasks.
| A | B |
|---|---|
| Task | Due Date |
| Complete report | 10/1/2026 |
| Review data | 10/5/2026 |
Select the due dates and apply a conditional formatting rule that uses the formula below. The rule compares each due date with today's date automatically.
=B2<TODAY()
When the date in B2 is earlier than today, the rule is true and Excel applies the chosen format to highlight the cell. Because the formula references B2 relatively, it adjusts for each row in the selected range, and the highlight moves as dates pass without any manual maintenance.
Formatting TODAY and NOW Results
TODAY and NOW return numbers, so the cell format decides how they look. If a result shows a number such as 46300 or 0.6 instead of a date or time, apply a date or time format.
| Format code | Example display |
|---|---|
| m/d/yyyy | 10/5/2026 |
| dddd, mmmm d, yyyy | Monday, October 5, 2026 |
| h:mm AM/PM | 2:30 PM |
| m/d/yyyy h:mm | 10/5/2026 14:30 |
Open the Format Cells dialog with Ctrl+1 and choose a date or time
category, or type one of the custom codes above. Use TEXT when the
formatted value must become part of a text string, as in the dynamic header
example.
TODAY and NOW Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Arguments supplied to the function | Too many arguments error | TODAY and NOW take no arguments; use empty parentheses |
| Result cell formatted as General or Number | A number such as 46300 or 0.6 appears | Apply a date or time format with Ctrl+1 |
| Date joined to text without TEXT | Header shows a serial number | Wrap the date in TEXT(date,"mmmm d, yyyy") |
| Workbook calculation set to Manual | Date or time will not update | File > Options > Formulas > Automatic |
| A changing value is needed as a permanent record | Timestamp changes on every recalculation | Use Ctrl+; or Ctrl+Shift+;, or Paste Special > Values |
| Time shown as a decimal such as 0.5 | No clock time appears | Format the cell as a time instead of a number |
The TODAY and NOW functions bring the current moment
into any spreadsheet. TODAY supplies a clean whole-day serial and
NOW adds the time fraction, and because both are volatile they
always reflect the present when Excel recalculates. Their numbers feed directly
into arithmetic, so countdowns, ages, elapsed times, dynamic headers, and
conditional formats are simple to build. Just remember the trade-off: the same
volatility that keeps them current also means they can never serve as a
permanent record. Freeze those values with the static shortcuts or Paste
Special > Values, and the two functions cover almost every date and time task
you will meet.
Author
List all Excel Formulas.