Conditional Formatting for time

[benmurgatroyd]

Hi,

I'm trying to set up a conditional format for cells to change colour based on a value in another cell. For example:

A2 is a time (06:00). In B2 and C2 there are two different times (07:15 & 07:45). I need the cell that is under 90 minutes (B2) to highlight green and the cell that is over 90 minutes (C2) to change red based on the time entered in cell A2.

I have tried multiple formats but none produce the results I'm looking for. Any help would be much appreciated.

Thanks

 

[Marcelo Branco]

Welcome to Mr Excel forum

You need two rules in CF

Select B2:C2

Rule 1
Home > Conditional Formatting > New Rule > Use a formula to determine...
Insert this formula
=B2=MEDIAN($A2,B2,$A2+TIME(1,30,0))
Format --> Green

Rule 2
Home > Conditional Formatting > New Rule > Use a formula to determine...
Insert this formula
=B2<>MEDIAN($A2,B2,$A2+TIME(1,30,0))
Format --> Red

Hope this helps

M.

 

[benmurgatroyd]

Thanks M.

That has worked great but the cells in B2 & C2 are coloured red even when no value has been entered (they change to red/green once I've entered a time in both cells). Some of the cells won't have values entered daily so when not in use I need them to show as white/empty. How can I get them to stay blank until I've entered the value which triggers the conditional format rules?

 

[Marcelo Branco]

Thanks M.

That has worked great but the cells in B2 & C2 are coloured red even when no value has been entered (they change to red/green once I've entered a time in both cells). Some of the cells won't have values entered daily so when not in use I need them to show as white/empty. How can I get them to stay blank until I've entered the value which triggers the conditional format rules?

Adjust the formula in Rule 2 to
=AND($A2<>"",B2<>"",B2<>MEDIAN($A2,B2,$A2+TIME(1,30,0)))

M.