Excel Practice
Lessons Lesson 80

Dates and Time: Putting It Together

About this lesson

Every date question in Excel comes back to the same fact. A date is a count of days. So subtracting gives days, adding gives a later date, and comparing sorts them correctly.

By the end you can

  • combine DATEDIF, TODAY and EOMONTH on one sheet.
  • choose the date function from the question being asked.

Practises DATEDIF EOMONTH TODAY TEXT DATE

The idea

The whole module on one sheet. A date minus a date is a number of days. A date minus a number is another date. Whole years and months need DATEDIF, because dividing by 365 is wrong near the boundaries. Month ends need EOMONTH, because months are not the same length. And anything meant to stay right tomorrow needs TODAY, not a typed date.

The failure to watch for is the quiet one. A comparison against an empty cell does not give an error. The empty cell counts as zero, and zero is not any real date. So the answer comes back FALSE on every row, for a reason that has nothing to do with the data. It looks entirely reasonable. Dates make that easy to miss. Nobody expects a date column to contain a number, which is exactly what it contains.

The mistake to watch for

A comparison against an empty cell. It does not give an error. The empty cell counts as zero, and zero is not any real date. So the answer comes back FALSE on every row, for a reason that has nothing to do with the data. When a date test is FALSE everywhere, check where its references point before you check the dates.

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

How many days does the Halstead contract run for? Put the answer in D2.

To begin, type it exactly:

=C2-B2

D2
Row A B C D E F G
1 Contract Signed Expires Answer
2 Halstead 45108 45473
3 Brayford 44927 45658
4 Wickholme 45332 45332
5
6 Notice days 90
7
8

Every step

  1. 1

    How many days does the Halstead contract run for? Put the answer in D2. To begin, type it exactly: =C2-B2.

    Hint. One minus the other.

  2. 2

    Notice has to be served the number of days in B6 before expiry. In D3, work out the latest date notice can be served on the Brayford contract.

    Hint. The expiry date and the notice period.

  3. 3

    Read =TEXT(EOMONTH(C2,0),"d mmm yyyy") and say what the end of the month the Halstead contract expires in is.

    Hint. Zero means this month.

  4. 4

    In D4, give the complete years the Brayford contract runs for.

    Hint. Earlier date first.

  5. 5

    D5 is meant to say whether the Wickholme contract expires on the day it was signed. It is answering something else. Repair it.

    Hint. Which row is the Wickholme contract on?

  6. 6

    In D6, use TODAY to say whether the Halstead contract has already expired. TRUE or FALSE.

    Hint. TODAY takes no arguments.