Conditional formatting for row if any dates in that row have elapsed.

Question

I am trying to set up conditional formatting that highlights an entire row if any dates in cells of that row have elapsed.

I tried preparing the conditional formatting formula in regular cells first to ensure my true/false statements are working.

My problem comes if there are blank cells in the range, I can't seem to figure out how to ignore blanks in the formula. As you can see below, the formulas work until blanks are introduced, I feel I am missing something simple but can't seem to figure it out.

Can anyone point me in the right direction? Thank you

Condition	Date 1	Date 2	Date 3	Date 4	Date 5	Date 6	Formula	Result
No date elapsed	9/07/2023	11/08/2023	31/08/2023	9/07/2023	31/12/2023	27/01/2023	=OR($B2:$G2<TODAY())	FALSE
1 date elapsed	9/07/2023	9/07/2022	31/08/2023	9/07/2023	31/12/2023	27/01/2023	=OR($B4:$G4<TODAY())	TRUE
No date elapsed with a blank	9/07/2023	9/07/2023	31/08/2023	9/07/2023		27/01/2023	=OR($B3:$G3<TODAY())	TRUE
1 date elapsed with a blank	9/07/2023	9/07/2022	31/08/2023	9/07/2023		27/01/2023	=OR($B5:$G5<TODAY())	TRUE

harun24hr · Accepted Answer

Andrew__K&nbsp;Try below formula-=OR(FILTER($A2:$F2,$A2:$F2&gt;0)&lt;TODAY())&nbsp;

Forum Discussion

Conditional formatting for row if any dates in that row have elapsed.

Share

Resources