Conditionnal Formatting with Dynamic Custom Formatted Date

Question

My problem is that I want to color in red a list Custom Formatted Dates when the date is overdue.

For instance, I got two actions:

• Buy Bread : Must be finished before 27/01
• Buy Milk : Must be finished before 25/03

My Excel looks like:

       A                B                C
1   Action            Date            Finished
2   Buy Bread         27/01           NO
3   Buy Milk          25/03           Yes

I want to have the Column B be Red if the date is outdated and the action NOT finished

I've used Conditional Formatting such as:

• Range is : "= $B:$B"
• Formula is : "If value in the cell is > TODAY()
• The cell must be RED only IF the action is marked as NO
• And, of course, Formatting is with the Red Color.

I can't use the "IF A3 is NO" condition properly in my conditionnal formatting formula. How can I put this IF with the "CURRENT CELL"?

For exemple:

IF ( CURRENTCELL in B2 > TODAY AND CURRENTCELL in C2 = NO )

Answer 1

In your conditional formatting window simply enter this formula:

=AND($B2<=TODAY(),$C2="No")

You may need to replace , with ; depending on your locality.

Note that conditional formatting works by checking a formula, and if the result is TRUE it applies the format. As a result you don't use IF statements, as these are used for applying a specified outcome if a condition is true.

An explanation of Conditional Formatting
The formula that you write is dynamic. Imagine you added a column at the end of your data, wrote the formula into the first cell and copied it down.

Because you didn't lock the row number, the formula changes to look at the row it is in. You may end up with 100 copies, each one looks at one of 100 rows.

This is the same, the range you selected is your column, and Excel modifies the conditional formatting to check each row separately. When it's checking row 6 the formula has changed to refer to B6.

This may be of help: chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics.

Edit
For future reference, modified for French: =ET($B2<=AUJOURDHUI();$C2="No")