proper writing of a formula

[YS1107]

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.

 

[Fred Smith]

What does an 8pm time look like? What does a 6pm time look like? Do your
cells have times in them, or text? Do you mean any time between 8pm and 9pm,
or some other range? Same for the 6pm time.

Regards,
Fred.

 

[YS1107]

The formula I have now, the time looks like for 8pm (>=20/24) and 6pm
(>=18/24). The times are choosen by a drop down list. The time range for this
formula is until 12am (midnight). The formula I have tried is
=IF($C$5:$C$6,$E$5:$E$6,$G$5:$G$6,$I$5:$I$6,$K$5:$K$6,$M$5:$M$6,$O$5:$O$6)>=20/24and>=18/24
and some other variations of it but none seem to work. Its says "Too many
arguments". At the present moment I am still trying to figure it out.

Thanks for helping me

 

[Fred Smith]

Here's how you check for one cell meeting either condition:
=if((or(c5=">=20/24",c5=">=18/24"),"yes","no")

The first thing to do is check this out to see if it works for you.

If it does, then we can work on the entire range.

Regards,
Fred

 

[YS1107]

This did not work.

At the moment the spreadsheet is formatted correctly for I want it to do
which is to change the color each individual set of cells to change based on
"time" of day for each day of the week. But now I need the just the times to
apply for the entire week. Here are the formulas for the spreadsheet I have
now which is for each day instead for the entire week.

=MAX($C$5:$C$6)>=20/24
=AND(MAX($C$5:$C$6)>=19/24,MAX($C$5:$C$6)<=19.5/24)
=OR($C$5:$C$6<18.5/24)

Wait I just thought maybe I can apply this set of formulas to the entire
week and change the times to suit my needs.

Thanks for helping me

 

[Fred Smith]

In order to get the help you need, you need to be specific about what your
data looks like.

You initially said you have ">=20/24" in the cell, because it came from a
drop down list. However, your formulas are checking for numbers, and by the
formulas you are using, you have Excel times in your cells. =20/24 will
check for a time greater than 8pm. In this case, any time greater than 8pm
is also greater than 6pm. So, if this is correct, all you have to do is
check for times past 6pm.

The more information you give, the better responses you will get.

Regards,
Fred

 

[YS1107]

What I need is for cells
$C$5:$C$6,$E$5:$E$6,$G$5:$G$6,$I$5:$I$6,$K$5:$K$6,$M$5:$M$6,$O$5:$O$6 to
format all cells $B$5:$O$6 based on this information. The information comes
from a drop down list which are times of the day.

If the forementioned cells have ONE 8:00pm (or later, till midnight) AND ONE
6:00pm (or later, till midnight) then all cells $B$5:$O$6 turn GREEN

If the cells have ONE 8:00pm (or later, till midnight) OR ONE 6:00pm (or
later, till midnight) then all cells $B$5:$O$6 turn YELLOW (which I guess it
would really mean 6:00pm or later?)

If the cells have none of the above criteria then all cells $B$5:$O$6 turn
red.

I hope I have stated this clearly enough.
Thank You

 

[Luke M]

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.

 

[YS1107]

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