Forum Discussion

AustinTurek's avatar
AustinTurek
Copper Contributor
Sep 17, 2024
Solved

Conditional Formatting Based on Date & Status

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!   

  • 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.

  • 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.

Resources