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

Occasional Contributor

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


ConditionDate 1Date 2Date 3Date 4Date 5Date 6FormulaResult
No date elapsed9/07/202311/08/202331/08/20239/07/202331/12/202327/01/2023=OR($B2:$G2<TODAY())FALSE
1 date elapsed9/07/20239/07/202231/08/20239/07/202331/12/202327/01/2023=OR($B4:$G4<TODAY())TRUE
No date elapsed with a blank9/07/20239/07/202331/08/20239/07/2023 27/01/2023=OR($B3:$G3<TODAY())TRUE
1 date elapsed with a blank9/07/20239/07/202231/08/20239/07/2023 27/01/2023=OR($B5:$G5<TODAY())TRUE
2 Replies
best response confirmed by Andrew__K (Occasional Contributor)

@Andrew__K Try below formula-




@Harun24HR Perfect! works great. Thank you very much