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

By Ali, Founder, Excel Lessons

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

Days Left and Deadlines Dates in Practice
Ages and Years of Service Dates in Practice
Grouping by Month: EOMONTH and SUMIFS Dates in Practice
Bold, AutoSum and the Date Stamp Keyboard Shortcuts

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

In `D2`, type:

=C2-B2

That is how long the Halstead contract runs.

Row	A	B	C	D
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

In D2, type: =C2-B2. That is how long the Halstead contract runs.

Hint. One minus the other.
2

A date minus a number gives an earlier date. In D3, subtract B6 from C3. That is the deadline for the Brayford contract.

Hint. The expiry date and the notice period.
3

C2 is when the Halstead contract expires. Read =TEXT(EOMONTH(C2,0),"d mmm yyyy") and say what it returns.

Hint. Zero means this month.
4

In D4, find B3 to C3's complete years with DATEDIF.

Hint. Earlier date first.
5

D5 should say if the Wickholme contract expires when signed. It answers a different question. Fix D5.

Hint. Which row is the Wickholme contract on?
6

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

Hint. TODAY needs no arguments at all.