HELP with conditional formatting and a max time
 

I have a spreadsheet that I am trying to get 8 cells to conditional format
based on the "highest" condition of one cell. I got 2 of the three conditions
to format but I can't get the "middle" condition to work. Here is what I have:
=MAX($C$5:$C$8)=0.833333333333333 this turns all 8 cells "GREEN"
="MAX($C$5:$C$8)<0.833333333333333" this is suppose to turn all 8 cells
"YELLOW" but this one does not work
="MAX($C$5:$C$8)<0.791666666666667" this turns all 8 cells "RED"

I have drop downs in the cells to pick "times" and based on the lastest time
it determines the "Color" of all cells. If any of the cells, c5-c8, are 8pm
or later all cells are green. If the latestYS1107 time is 7pm or 730pm then I
want all cells to be yellow and if all cells are 630pm or earlier then all
cells red.

I hope this is clear enough, but I don't fully understand excel but I could
use some help. Thanks in advance!

HELP with conditional formatting and a max time
 

Hi,

Your conditions and your discussion don't match each other - if any cell
greater then 8 PM:

=MAX($C$5:$C$8)=20/24


The second conditon (is you want all cells a certain color if the max of the
range is between 7 and 7:30?) If so

=AND(MAX($C$5:$C$8)=19/24,MAX($C$5:$C$8)<=19.5/24)

again I don't think this

The third conditon you say if ALL cells are earlier than 6:30, but you again
use MAX which suggests you are looking for any cell being before 6:30??

If you are looking for any cell beign before 6:30 then

=OR($C$5:$C$8<18.5/24)

If you are looking for all cells < 6:30 then

=AND($C$5:$C$8<18.5/24)

If you are looking for the latest cell being before 6:30 then

=MAX($C$5:$C$8)<18.5/24
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"YS1107" wrote:

I have a spreadsheet that I am trying to get 8 cells to conditional format
based on the "highest" condition of one cell. I got 2 of the three conditions
to format but I can't get the "middle" condition to work. Here is what I have:
=MAX($C$5:$C$8)=0.833333333333333 this turns all 8 cells "GREEN"
="MAX($C$5:$C$8)<0.833333333333333" this is suppose to turn all 8 cells
"YELLOW" but this one does not work
="MAX($C$5:$C$8)<0.791666666666667" this turns all 8 cells "RED"

I have drop downs in the cells to pick "times" and based on the lastest time
it determines the "Color" of all cells. If any of the cells, c5-c8, are 8pm
or later all cells are green. If the latestYS1107 time is 7pm or 730pm then I
want all cells to be yellow and if all cells are 630pm or earlier then all
cells red.

I hope this is clear enough, but I don't fully understand excel but I could
use some help. Thanks in advance!

HELP with conditional formatting and a max time
 

THANK YOU SHANE!!!

In a matter of 30 minutes you have solved over 3 months of heart ache for me
and I truly THANK YOU for your expertise. THANK YOU SHANE!! You are a
GENIUS!!!

"Shane Devenshire" wrote:

Hi,

Your conditions and your discussion don't match each other - if any cell
greater then 8 PM:

=MAX($C$5:$C$8)=20/24


The second conditon (is you want all cells a certain color if the max of the
range is between 7 and 7:30?) If so

=AND(MAX($C$5:$C$8)=19/24,MAX($C$5:$C$8)<=19.5/24)

again I don't think this

The third conditon you say if ALL cells are earlier than 6:30, but you again
use MAX which suggests you are looking for any cell being before 6:30??

If you are looking for any cell beign before 6:30 then

=OR($C$5:$C$8<18.5/24)

If you are looking for all cells < 6:30 then

=AND($C$5:$C$8<18.5/24)

If you are looking for the latest cell being before 6:30 then

=MAX($C$5:$C$8)<18.5/24
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"YS1107" wrote:

I have a spreadsheet that I am trying to get 8 cells to conditional format
based on the "highest" condition of one cell. I got 2 of the three conditions
to format but I can't get the "middle" condition to work. Here is what I have:
=MAX($C$5:$C$8)=0.833333333333333 this turns all 8 cells "GREEN"
="MAX($C$5:$C$8)<0.833333333333333" this is suppose to turn all 8 cells
"YELLOW" but this one does not work
="MAX($C$5:$C$8)<0.791666666666667" this turns all 8 cells "RED"

I have drop downs in the cells to pick "times" and based on the lastest time
it determines the "Color" of all cells. If any of the cells, c5-c8, are 8pm
or later all cells are green. If the latestYS1107 time is 7pm or 730pm then I
want all cells to be yellow and if all cells are 630pm or earlier then all
cells red.

I hope this is clear enough, but I don't fully understand excel but I could
use some help. Thanks in advance!