Conditional Formatting- Date

Resolved · Medium Priority · Version 2016

Fri 28 Feb 2020, 11:10 Delegate Charlotte said...

Charlotte has attended:
Excel Intermediate course
Outlook Advanced course
Word Advanced course
Office 365 End User course

Conditional Formatting- Date

I'm creating an action tracker in Excel. I want to create a rule for marking overdue open actions in red. This part seems to work when I create a conditional formatting rule, but I don't want actions marked red when they are complete/ closed (separate column), and I don't want blank fields yet to receive data to be marked red. Any advice would be gratefully received. I couldn't find guidance in the training manual.

Fri 28 Feb 2020, 19:17 Trainer Jens said...

RE: Conditional Formatting- Date

Hi Charlotte,

Thank you for the forum question.

You will need a custom rule to do what you want. If you click New Rule in the Conditional Formatting dialog box and click on the option "Use a formula to determine which cells to format".

If you have your due dates in column A starting from A2 and "Complete" or "closed" in column B the formula below will do what you want.

=And(A2<Today(),or(B2<>"Complete",B2<>"closed"))

I hope this makes sense

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector