Excel track · Exercise 70 of 86 Paid
TODAY and NOW: the volatile clock
Tell today's date from this moment, and read the clock out of a date number.
Intermediate
Invoices dated by day, and one log stamp timed to the minute
Read-only data · screen 1 of 2 · answer in C2
In C2, work out how many days INV-1041 has been open, counting up to the day this exercise pins as today (15 March 2024).
| A | B | C | |
|---|---|---|---|
| 1 | Invoice | Raised | Days open |
| 2 | INV-1041 | 45352 | |
| 3 | INV-1042 | 45358 | |
| 4 | INV-1043 | 45361 | |
| 5 | INV-1044 | 45364 |
In B3, return the hour the stamp in B2 was taken at, with the exercise's clock pinned to 14:30.
| A | B | |
|---|---|---|
| 1 | Reading | Value |
| 2 | Log stamp | 45366.604166667 |
| 3 | Hour of the stamp |
Reference answer
What it does
TODAY and NOW read the clock of the machine the workbook is open on, so their answer can change when nothing in the sheet has. TODAY returns the day as a whole serial, 45366 on 15 March 2024. NOW returns that day with the time since midnight in the fraction, so 14:30 is 45366.604166667. Both recalculate on every edit; Excel calls functions that do this volatile.
This sheet asks for days open, which is a subtraction. TODAY minus the raised date in B2 gives 14 for INV-1041. The second screen pulls the hour out of a stamp, because HOUR reads the fraction rather than the day. Building the first column on NOW leaves part of a day on the count.
Reach for TODAY when the answer is a date and NOW when the time of day matters. Volatile formulas recalculate on every edit, so a large sheet full of them is slower than one that reads a typed date from a single cell.
Syntax
=TODAY() =NOW() =HOUR(date number)Worked example
On Invoices dated by day, and one log stamp timed to the minute, =TODAY()-B2 returns 14.
=TODAY()-B2Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | HOUR was given a label rather than a date number, or the raised date in B2 was stored as text. |
#NAME? | TODAY or NOW typed without the empty brackets, so Excel reads the name as an unknown word. |
Fraction of a day | NOW keeps the time in the decimal, so a days-open column built on it shows 14.6 for a job that is 14 days old. |
At work
An ageing report that counts days open each time the file is opened, and a log line stamped with the moment it was written.