Conditional format based on three dates

[pete4monc]

Hi all
I have looked through multiple threads and not managed to find one that will help my cause, I have tried AND/IF & OR formulas but can't sem to get the formula right.
I'm sure this is simple but I am new to excel formulas.

Currently I have a cell (L2) that looks at two other cells (H2 & I2) and conditional formats L2 to colour the cell red and display text "send email" when the dates in H" & L2 expire.
current formula is =OR($H2<TODAY(),$I2<TODAY())

I need L2 cell to also look at cell N2 and format L2 the same as above if the date in N2 has also passed.

Thanks in advance for any help.

 

[etaf]

i may not be following exactly
=OR($H2<TODAY(),$I2<TODAY())
include N2
=OR($H2<TODAY(),$I2<TODAY(),$N2<TODAY())

if any cell is blank it will se that as less than today
do they all have dates in

otherwise
=OR(AND($H2<TODAY(),$H2<>""),AND($I2<TODAY(),$I2<>""),AND($N2<TODAY(),$N2<>""))

conditional formats L2 to colour the cell red and display text "send email"

conditional formatting can not set a Cell to display any text
so it may be an IF statement you need

what have you tried ???

 

[pete4monc]

i may not be following exactly
=OR($H2<TODAY(),$I2<TODAY())
include N2
=OR($H2<TODAY(),$I2<TODAY(),$N2<TODAY())

if any cell is blank it will se that as less than today
do they all have dates in

otherwise
=OR(AND($H2<TODAY(),$H2<>""),AND($I2<TODAY(),$I2<>""),AND($N2<TODAY(),$N2<>""))


conditional formatting can not set a Cell to display any text
so it may be an IF statement you need

what have you tried ???

Hi Etaf

Thanks for your help.

I have found my mistake...............don't laugh!!
I had the same formula in both the cell as well as the conditional format and they were cancelling each other out.

At the moment the cell has the formula
=IF(OR($H2<TODAY(),$I2<TODAY(),$N2<TODAY()),"SEND QUESTIONNAIRE","")
and then the cell is conditional formatted to colour red when the text "SEND QUESTIONNAIRE" is shown.


It is now sorted but thank you for your input.
Pete

 

[etaf]

you are welcome