Formatting cells based on date < date

[Laila3006]

Hello

I have a spreadsheet with dates in collum E, P and S.
The dates in Collumn E and P are Manually entered. The dates in Colums S are based on culumn E with the simple formular =E:E+91.
Collumn E indicate when the different projects starts.
Collumn P are delivered dates and
Culum S are deadline dates.

I want the cells in S to have a green color if the deadline is held. So if S>P the cell sould be green. and if S<P is should be red.
The deliverydate and deadline date is different in all rows.

Can anyone help with the formular to enter i the conditional formatting box?

Thanks

 

[istiasztalos]

Hi Laila,

I'd recommend you set column by default to green, and use one rule to change to red when deadline is missed, that way you only need one formatting rule.

When setting up the rule select the entire column you want to color, then add the formula =IF(S2<P2) -supposing that you start data in row 2- and make sure you don't add $ sign and finally, select the color format you want. When a rule is applied to several cells, these cell references are treated as relative reference, so in row 5 excel will compare S5 to P5, in row 15 it compares S15 to P15 and so on...

 

[istiasztalos]

I just noticed my answer was cut, the formula would be =S2<p2 (assuming="" your="" data="" starts="" in="" row="" 2)
< P2
</p2>Make sure you don't add $ sign, this way it is a relative reference

Hi Laila,

I'd recommend you set column by default to green, and use one rule to change to red when deadline is missed, that way you only need one formatting rule.

When setting up the rule select the entire column you want to color, then add the formula =IF(S2<p2) -supposing="" that="" you="" start="" data="" in="" row="" 2-="" and="" make="" sure="" don't="" add="" $="" sign="" finally,="" select="" the="" color="" format="" want.="" when="" a="" rule="" is="" applied="" to="" several="" cells,="" these="" cell="" references="" are="" treated="" as="" relative="" reference,="" so="" 5="" excel="" will="" compare="" s5="" p5,="" 15="" it="" compares="" s15="" p15="" on...[="" quote]

</p2)>