Conditional Formatting Formula

[Andrew0]

Hi all,

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.

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​

Can anyone point me in the right direction?

Thank you in advance.

 

[Marcelo Branco]

Welcome to MrExcel Forum

Try
=MIN($B2:$G2)<TODAY()

M.

 

[Andrew0]

Welcome to MrExcel Forum

Try
=MIN($B2:$G2)<TODAY()

M.

oh WOW! it was that easy, i knew i was missing something simple. Thank you so much! worked perfectly.

 

[Marcelo Branco]

You're welcome. Thanks for the feedback;

M.

 

[Andrew0]

You're welcome. Thanks for the feedback;

M.

MIN works great, until there is no data in that range of cells... Any idea on a work around?

Thank you

 

[Marcelo Branco]

Could you provide a scenario where the formula above doesn't work?

M.

 

[Andrew0]

Could you provide a scenario where the formula above doesn't work?

M.

No problem, thank you for helping.

As per below, formula is returning 'true' for cells with elapsed date. However, it is also returning 'true' for rows with no dates in that range.

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​	=MIN($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​	=MIN($B3:$G3)<TODAY()	TRUE​
No date elapsed with a blank	9/07/2023​	9/07/2023​	31/08/2023​	9/07/2023​		27/01/2023​	=MIN($B4:$G4)<TODAY()	FALSE​
1 date elapsed with a blank	9/07/2023​	9/07/2022​	31/08/2023​	9/07/2023​		27/01/2023​	=MIN($B5:$G5)<TODAY()	TRUE​
No dates entered							=MIN($B6:$G6)<TODAY()	TRUE​

 

[Marcelo Branco]

Try
=AND(COUNT($B2:$G2),MIN($B2:$G2)<TODAY())

M.

 

[Andrew0]

Try
=AND(COUNT($B2:$G2),MIN($B2:$G2)<TODAY())

M.

Worked! Thank you! I Have no idea how they work together but thank you!

 

[Andrew0]

Thought I should share another solution someone gave me from somewhere else which also worked.

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

Thanks again @Marcelo Branco