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

By Ali, Founder, Excel Lessons

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.

Read more about this

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() returns today's date. It updates whenever the sheet opens.

In C2, type:

=B2-TODAY()

That is the days until the fleet policy renews.

C2
Row A B C D E F G
1 Policy Renews Days left
2 Fleet cover 46348
3 Premises 46379
4 Employers 46331
5 Cyber 46334
6
7
8

Every step

  1. 1

    TODAY() returns today's date. It updates whenever the sheet opens. In C2, type: =B2-TODAY(). That is the days until the fleet policy renews.

    Hint. Empty brackets, and a subtraction.

  2. 2

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

    Hint. It is the earlier date minus today.

  3. 3

    In C5, compare B5 with TODAY. Shape: =cell=TODAY(). It returns TRUE or FALSE.

    Hint. Compare the two dates with an equals sign.

  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, say if the premises policy renews in more than 30 days. Shape: =cell-TODAY()>n. It returns TRUE or FALSE.

    Hint. Work out the days left, then compare it to 30.