Conditional formatting - various rules in one cell

Hi - Please find attached my sample.

This is basically a variety of steps - required timescales (SLA's) and action completed dates. hopefully you can follow it. Point 1 is definitely a conditional formatting but im not sure if anyone can help me with point 2 & 3 here also? I am aware that these are What if functions but I don't want to post this twice and contravene rules.

to be formatted in all the columns which are (received)

Point 1)
**if 2a [J2] is before 2a [I2] the highlight green as per sample.
**if 2a [J3] is later than 2a [I3+7days] the highlight red as per sample.
**if 2a [J4] is later than 2a [I4] but before [I4+7days] the highlight amber as per sample.

Point 2)
Column M - if using the rules above the Timescale has been met or not with [yes/no/nearly] using an IF function? this will enable me to run pivot table at a later date (which I know how to do)

Point 3)
4a - date needs to populate as the later of 1) 2b - received [K] or 2) Step 7 [Z]

Thanks for your help!!

Originally Posted by mehmetcik

1. Select J2 to J4 and select conditional format, new rule, use formula to determine format.

You will need to do this three times to enter three conditional format rules

a:

Formula:

Please Login or Register  to view this content.

Fill Color Green
b:

Formula:

Please Login or Register  to view this content.

Fill Color Red
c:

Formula:

Please Login or Register  to view this content.

Fill Color Amber

2. Your sample results for M2 to M4 do not read right:

Nearly
No
Yes


I expected

Yes
No
Nearly


Formula for M2

Formula:

Please Login or Register  to view this content.

Formula for R2 if your example is correct

Formula:

Please Login or Register  to view this content.

Corrected Formula for R2.

Formula:

Please Login or Register  to view this content.

Hi - this is fantastic and a massive help!! and works for the majority of the points.

I have uploaded my sample updated with the information you have given me but im still doing something wrong.

For instance - if oi update J10 from 14/07/17 to 15/07/17 the Cell J15 changes? and I have no clue as to why?

also I didn't account for No also being a response in the received columns - which should ordinarily be left blank with no colour.

and J17 should also be Amber not red - which tells me I have definitely done something wrong.

and just a query?? when I update a date im assuming the cell colour updates automatically? without the need to refresh??

Thanks for your help - this has been invaluable tonight as I have been trying this for ages!

Lorna B

THe only problem I am having is the cell does not go white (or blank) when No is an option. (it goes red) and when blank (goes green)

the range now has the following rules.
=AND($J$2<>"No",$J$2>$I$2,$J$2<I+7) - BLANK
=AND(J2>I2,J2<I2+8) - AMBER
=J2>$I$2+7 - RED
=J2<=I2 - GREEN

THanks for your help.

so I have copied these rules to a completely new worksheet (the actual workbook I need) but it doesn't seem to be working. and its exactly the same columns as the sample doc! arrggghhh - what have I done wrong!

Originally Posted by Lorna B

so I have copied these rules to a completely new worksheet (the actual workbook I need) but it doesn't seem to be working. and its exactly the same columns as the sample doc! arrggghhh - what have I done wrong!

Ok ok - so I got it working!!! silly me with all the $$$$

but - on a serious note the only rule that is not working is the following one.

=AND(J1<>"No",J1>I1,J1<I+7)

I want this to show as formatted white (or blank) if the cell is blank or has No

Thanks again

No neither are working - hummm.