2 step conditional formatting

Question

I'm working on a tracking document with conditional formatting. A date of assessment will be entered in D2 (e.g. 13 Nov 18) and F2 will automatically populate the date 6 months from the date in D2 (e.g. 12 May 19). I need a F2 to turn red if the date in F2 has passed today's date (e.g. 12 May 19 would turn red on 13 May 19), but to turn white if ANY date is entered in G2 (e.g. 14 May 19). I can get both rules to work in conditional formatting but not together and I don't want F2 to be red unless the 6 month mark has passed. Any help with this puzzle is appreciated :)

Answer 1

Hello there -

I believe the easiest way is to create another column to check the conditions.

For example, you can enter this in cell H2 (assuming it is not occupied yet):

H2 = IF(ISBLANK(G2), IF(F2>=NOW(), 2,0),1)

In this way, H2 will equal 1 whenever G2 is not blank, equal 2 whenever G2 is blank and F2 has passed today's date, and equals 0 otherwise.

Then, you can apply conditional formatting to F2 using value in H2.

Does this help your question?

Answer 2

Hi

Thank you for reaching out to us. I'm so sorry you are facing issues!

I'm Maggie, an Independent Advisor from thr user community. I love helping other users in the Community by sharing my experience with Microsoft products.

I would recommend to use conditional formatting with formulas: https://support.office.com/en-us/article/use-fo...

Hope this helps!

I wish you a great day/night :)!

Maggie

Answer 3

Select F2.

Delete any existing conditional formatting rules for this cell.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=AND(F2<TODAY(),G2="")

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.