Days Left and Deadlines

About this lesson

Days left until a deadline is the due date minus the as-of date. Dates are serial numbers, so the result is negative once the deadline has passed.

By the end you can

work out the days left until a deadline against an as-of date.
flag overdue items with an IF on the date.

Practises DAYS IF TODAY DATEDIF

The idea

Everything about deadlines follows from dates being numbers. Due minus as-of is the days left. Below zero means overdue. Earlier is less-than. DAYS(end, start) is the same subtraction with the arguments named. It is the version to use when the sheet will be read by somebody else.

The as-of date is in a cell on purpose. TODAY() would make every figure move overnight. That is what you want on a live tracker, and not what you want on a report that says "as of 19 March". Put the date in one cell, lock it, and change it on purpose.

The mistake to watch for

Flipping the sign of an overdue task. Days left below zero is the information. Keep the negative number, and a test for less than zero finds every late task. And put the as-of date in a locked cell instead of typing TODAY into every formula. Then a report that says "as of 19 March" still says that next week.

Where this comes up again

Date Arithmetic: Subtracting Dates Dates and Time
DATEDIF: The Undocumented One Dates and Time
Dates and Time: Putting It Together Dates and Time
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

The report date is in B7. In D2, work out how many days are left until the draft is due. That is the due date minus the as-of date, with the as-of date locked.

To begin, type it exactly:

=B2-$B$7

Row	A	B	C	D	E
1	Task	Due	Due shown	Days left	Status
2	Draft	45380	29 Mar
3	Review	45373	22 Mar
4	Sign-off	45400	18 Apr
5	Publish	45366	15 Mar
6
7	As of	45370	19 Mar
8

Every step

1

The report date is in B7. In D2, work out how many days are left until the draft is due. That is the due date minus the as-of date, with the as-of date locked. To begin, type it exactly: =B2-$B$7.

Hint. Due minus as-of.
2

In D3, the same thing for the review, with the DAYS function. It takes the end date first and the start date second.

Hint. End first, start second.
3

Publish was due before the report date. Read =B5-$B$7 and say what it returns.

Hint. Earlier minus later.
4

In E2, mark the draft Late if its due date is before the as-of date. Otherwise mark it On time.

Hint. Less than means earlier.
5

In E5, write a formula using IF for the publish task. Show Overdue when its due date is past. Otherwise show the number of days left.

Hint. Text in one branch, subtraction in the other.