Conditional Formatting Based on Date & Status

[AustinTurek]

Hello!

I need some help getting my conditional formatting to function properly. I would like the cells in the "Due Date" column to highlight red if the date is less than today AND the "Status" column does not read "Done". Problem is, I can't even get the conditional formatting to work correctly on just the date portion of things, much less the status qualifier. I've tried both '=$D2<NOW()' as well as '=$D2<TODAY()' and the results are nonsensical. Thanks in advance for the help!   

[#2 · Accepted solution]

@AustinTurek 

Select from D2 down. D2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=AND($F2<>"Done", $D2<>"", $D2<TODAY())

Click Format...
Activate the Fill tab.
Select red as fill color.
Click OK, then click OK again.

[#3]

@HansVogelaar Much appreciated! Works perfectly!