Color-highlighting cells with dates which pass deadlines

[Deicide5997]

Hi experts,
I have a large to-do list with deadline dates associated with certain tasks.
I am trying to figure out how I can find a function in excel which will help me highlight with red color the cells which have passed the deadline, so that I can see them more clearly.
Any good hints?
Thanks a bunch!

[AliGW]

You will need to set a conditional formatting rule. CF is on the main ribbon.

[Deicide5997]

Thanks,
but option would you recommend to choose, so that the dates would be compared always with today's date (not a fixed date) and the ones passed the deadlines would be highlighted?

[AliGW]

Will you please attach a sample Excel workbook? Nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Deicide5997]

Hi,
Many thanks for your follow up. I have made up an example of what I am looking for in terms of the solution. Hope the attachment is accessible, I have followed your recommendation.
Thanks again!
Dennis

[AliGW]

The dates marked in red are before the deadlines ...

[Deicide5997]

Sorry,
I might have been unclear. Please ignore the dates in the merged cells. The function I am looking for applies to cells E4, H4, E7 and H7.
As you can see, today's date is 26.10.2017, so the deadlines in E4 is past due, i.e. the action has not been completed until today, but was supposed to be completed on the 30.09.2017.
H4 and H7 on the other hand are still deadlines in the future, so they are not highlighted.
E7 is red in font color (because the deadline is before today's date), but the cell is highlighted green, because the action is completed.

Is it better explained?
Cheers Dennis

[AliGW]

OK - for each of those cells set a conditional formatting rule using the second option down in the dialog - 'Format only cells that contain'. From the drop-downs choose 'Cell value' and 'is less than' and add this to the final box: today()

Now set the font colour to red. To check it's working, you'll want to set the cells' font colour back to black BEFORE setting up the CF rules.

EDIT: You might want to do this using the formula option, so for example for E4:

=E4>C3 (where C3 is the cell contining the deadline date - change to suit). You will need a similar rule for the other date cells.

[Deicide5997]

that was quick!
a strange thing though: if I put a date "29.10.2017" as condition for the check on H4 and H7, the function works (H4 changes to red, whilst H7 remains white). However, if I put today() in stead of the date, both become red.

[AliGW]

You might be better off using the formula option, as I mentioned above.

[Deicide5997]

that becomes a bit more complicated, as I want the table to behave automatically and check with the current today-date, not by defining every cell individually.
never mind, I don't think it works properly in Excel anyway.
quite strange

[AliGW]

Create the formula using today():

=E4>TODAY()

It will work properly if you've got it right.