Avatar of Dave KIlby
Dave KIlby
Flag for Canada asked on

Excel Conditional Formatting

I have two conditional formatting questions

1 - I have a column with percentage values, If the percent is less than 92% but greater than 85% i want the background to be yellow, and then if it is less than 85% the background would be red.

2 - I have 2 columns B & C that have time values - I want to compare C to B and if C is great than B by 5 mins then the background would be red - example Column B = 9:00:00, Column C = 9:06:52.  Column C background would be red

How can I accomplish this?
Microsoft Excel

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon

I suggest using a formula criteria to determine which cells to format.

For the first one, let's assume that cell D2 is the first cell that needs conditional formatting. You might use formulas like:
=D2<0.85    for the red format
=AND(D2>=0.85, D2<0.92)     for the yellow format
After entering the formula, click the Format button, go to the Fill tab and choose your color. Click OK twice.

For the second one, you might use a criteria like:
=AND(COUNT(B2:C2)=2, (C2-B2)>=TIME(0,5,0))     for the red format
Dave KIlby

Thank you - what if I wanted to check column D and E which are also time values, but I want to check if column E is 5 or more mins less than column D then format red

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

For the percentage check you can use one of the standard conditional formatting setups and don't need to use the formula based check.

See screenshots below:
CF Rule 1CF Rule 2CF Result
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Rob Henson

Also, are you aware of the Conditional Formatting where you can add icons to the cell rather than filling the cell with a colour.
CF Icons