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

By Ali, Founder, Excel Lessons

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

In `D2`, type:

=B2-$B$7

B7 holds the as-of date, locked so it does not move.

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

In D2, type: =B2-$B$7. B7 holds the as-of date, locked so it does not move.

Hint. Due minus as-of.
2

In D3, use DAYS with B3 and B7. It takes the end date first and the start date second.

Hint. End first, start second.
3

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

Hint. It is earlier minus later.
4

In E2, use IF to show Late if B2 is before B7. Shape: =IF(test, if_true, if_false). Otherwise show On time.

Hint. Less than means earlier.
5

In E5, use IF to show Overdue if B5 is before B7. Shape: =IF(test, "text", value). Otherwise show the days left. Remember: text goes in quotes, numbers do not.

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