Conditional Formatting comparing dates

Closed
SLD - Jan 13, 2015 at 09:09 PM
 RayH - Jan 14, 2015 at 11:22 AM
I have two columns, that each have dates in them. I want to take Column A, subtract 100 days, then compare it to Column B. If Column B is less than Column A (date - 100) I want to mark it in red. If its greater than Column B, then mark it green.

Is there anyone that can help me with this?

thanks!!

1 reply

Using the "Use a formula to determine which cells to format" rule of Cond. Formatting.

Color this range in Red.
=(($B1:$B10)-$D$1)<($A1:$A10)

Color this range in Green.
=(($B1:$B10)-$D$1)>($A1:$A10)


Applies to range:
=$B$1:$B$10

The $10 should be change to the number of row you are working with.

If the dates match then no color will be applied, so if needed use <= or >= in place of < or >

Where $D$1 contains the value to take off, in your example the value would be 100.
This will enable you to be flexible with the date differences.
2