Help with conditional formatting formula

**Marlowe Nots** · 03-05-2010, 07:40 AM

Hi,
I'm sure this will be easy for someone, but it has me going bold!

I'm trying to format a cell based on a calculation of days from today + a date in a cell, plus the text in another cell. There's an "or" in that the cell needs to be highlighted based on;

The text cell not stating "Rarely" and the date being over 360 days old

OR

The text cell being equal to "Rarely" and the date being over 720 days old

I've tried:

=IF((E4-TODAY())<-360,B4<>"Rarely")=OR((E4-TODAY())<-720,B4="Rarely")

And

=IF(E4-TODAY()<-360,B4<>"Rarely",IF(E4-TODAY()<-720,B4="Rarely"))

But they don't work completely. Any help much appreciated.

Thanks!

**NBVC** · 03-05-2010, 07:51 AM

Try maybe:

=OR(AND(E4<TODAY()-360,B4<>"Rarely"),AND(E4<TODAY()-720,B4="Rarely"))

**Marlowe Nots** · 03-05-2010, 08:18 AM

Superb! Thank you very much. The only issue I have now is when the date cell (e4) is blank, Excel calculates the date from 1900. Any way of getting it to ignore blanks?

Thanks again!