Excel Practice
Lessons Lesson 74

TODAY and NOW

About this lesson

TODAY returns the current date as a serial number. NOW returns the current date with the time of day added as a decimal fraction.

By the end you can

  • work out days until a date with TODAY.
  • say when TODAY belongs on a sheet and when a fixed date is better.

Practises TODAY NOW

The idea

Neither takes any arguments. Both recalculate whenever the sheet does, which is the point. A countdown written with TODAY is right tomorrow. The same countdown with a date typed into it is wrong tomorrow and looks the same.

The difference between the two catches people. TODAY is a whole number. NOW is that number plus a fraction for the time of day. So =NOW()=TODAY() is FALSE at every moment except midnight, and a lookup against NOW will never match a date in a table. Use TODAY for anything to do with days. Use NOW only when you really want a timestamp. And be aware that "recalculates whenever the sheet does" means a value that changes under you. A cell recording when something happened should have the date pasted into it as a value. A TODAY formula there will say tomorrow tomorrow.

The mistake to watch for

Comparing against NOW when you mean today. NOW carries the time as a fraction. So it never equals a date in a table, and a lookup against it never matches. And a cell recording when something happened must hold a pasted value, not a TODAY formula. Otherwise it will say tomorrow tomorrow.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

TODAY() gives today's date, whenever the sheet is opened. In C2, work out how many days are left before the fleet policy renews.

To begin, type it exactly:

=B2-TODAY()

C2
Row A B C D E F G
1 Policy Renews Days left
2 Fleet cover 46289
3 Premises 46320
4 Employers 46272
5 Cyber 46275
6
7
8

Every step

  1. 1

    TODAY() gives today's date, whenever the sheet is opened. In C2, work out how many days are left before the fleet policy renews. To begin, type it exactly: =B2-TODAY().

    Hint. Empty brackets, and a subtraction.

  2. 2

    The employers' policy has already lapsed. Read =B4-TODAY() and say what it returns.

    Hint. Earlier minus later.

  3. 3

    In C5, say whether the cyber policy renews today. TRUE or FALSE.

    Hint. One comparison, no function around it.

  4. 4

    NOW() is TODAY() with the time of day on the end. Read =NOW()>=TODAY() and say what it returns.

    Hint. One is a whole number and the other is not.

  5. 5

    In C3, write a formula using TODAY that says whether the premises policy renews more than 30 days from now. TRUE or FALSE.

    Hint. Days remaining, then a comparison.