SOLVED

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

Brass 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 (Brass Contributor)
Solution

@Andrew__K Try below formula-

=OR(FILTER($A2:$F2,$A2:$F2>0)<TODAY())

Harun24HR_0-1668393230861.png

 

@Harun24HR Perfect! works great. Thank you very much

1 best response

Accepted Solutions
best response confirmed by Andrew__K (Brass Contributor)
Solution

@Andrew__K Try below formula-

=OR(FILTER($A2:$F2,$A2:$F2>0)<TODAY())

Harun24HR_0-1668393230861.png

 

View solution in original post