This works really really well except for one thing. For the green formatting
the cells turn GREEN if there is two times after 6pm, but I need for the
cells to turn green only if there is a 6pm or later AND at least a 8pm or
later. Right now if the cells have say, 730pm and 630pm all cells turn GREEN
but the this is not what I need. This condition would be yellow. The 8pm time
is the KEY time in addition to the 6pm or later. I hope this makes sense and
is possible. The TWO KEY conditions are at least ONE 6pm or later and the
other condition has to be ONE 8pm or later.

Luke Thanks So Much

"Luke M" wrote:

As was pointed out, since your checking for times until midnight, you really
only need to check for times past 6 pm.

First conditional format formula:

=SUM(--(--IF(MOD(COLUMN($C$5:$O$6),2)=1,$C$5:$O$6)=18/24))=2
Format green

2nd formula:
=SUM(--(--IF(MOD(COLUMN($C$5:$O$6),2)=1,$C$5:$O$6)=18/24))=1
Format yellow

3rd formula:
=SUM(--(--IF(MOD(COLUMN($C$5:$O$6),2)=1,$C$5:$O$6)=18/24))=0
Format red.



The MOD(COLUMN section is to eliminate the even numbered columns from the
C:O range, thus only working with the cells your actually concerned with.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"YS1107" wrote:

I have a formula that I want to write that the cells meet the conditional
format for times of day. I struggle to figure the "correct" language in an
excel formula.

I am struggling to write a formula for cells B5:O6 that if cells
C5:C6,E5:E6,G5:G6,I5:I6,K5:K6,M5:M6,O5:O6 have at least one 8pm time AND at
least one 6pm time (or later) the cells are green.

or if the cells have at least one 8pm or 6pm (or later) then all cells are
yellow

or if the cells have none of these conditions then all cells are red.

If anyone can enlighten me it would be much appreciated.

I know this involves "AND" and "OR" but I struggle with the correct format.