Add a formula to and existing formula

[Marty]

Hi
I have the following formula =AND($A$6="w",$F$6>TIME(7,0,0))+$F$6
which is great. If the letter W is added into cell A6 and the time is
greater than 7 hours in cell F6, F6 will be shaded in red. If no W is added
in A6 this rule does not apply.
Now what I want to do is still using the same cells and still having the
rule above active add more to it. For example if the letter P is added
instead of a W and a time is greater than 10 hours then the cell will change
to blue. I have tried to add =AND($A$6="p",$F$6>TIME(10,0,0))+$F$6 to the one
above but keep getting an error message.

Please help.

 

[OssieMac]

I wonder if I understand your question correctly.

=AND($A$6="w",$F$6>TIME(7,0,0))+$F$6

I don't understand. How can $F$6 ever be greater than 7 hrs + $F$6? (I
didn't think that you can have a negative time so it would always be greater
unless $F$6 is zero)
I would think that the formula should be:-
=AND($A$6="w",$F$6>TIME(7,0,0)) if you are testing $F$6 for greater than
7hrs in which case you simply add a second condition with the new formula to
the conditional formatting.
To add a second condition in pre xl2007, click the Add>> button.
In xl2007, click New rule but note that the new rule inserts at the top and
you use the arrows out to the right to move the rule to the required position.

 

[T. Valko]

=AND($A$6="w",$F$6>TIME(7,0,0))+$F$6

=AND($A$6="p",$F$6>TIME(10,0,0))+$F$6

Is there a specific reason why you're adding F6 to the end of the formula?

The result of the AND function will be either TRUE or FALSE and those
logicals will convert to 1 or 0 when added to F6. If F6 was 3:00 PM the
result of the AND function would be FALSE yet the format would still be
applied because F6 contains a number and any number other than 0 evaluates
as TRUE in logical terms.

What you need to do is add a second condition to the conditional formatting.

Use as the first condition (determine if you really need that +$F$6):

=AND($A$6="w",$F$6>TIME(7,0,0))+$F$6

Use as the second condition (determine if you really need that +$F$6):

=AND($A$6="p",$F$6>TIME(10,0,0))+$F$6

 

[Marty]

Thank you I will give it a go.

Some more info:
A6 is either to be W or P only.
B6 is blank
C6 is strart date and time
D6 is end date and time
E6 blank
F6 is D6-C6 with the conditional format applied. Therefore if W is entered
in A6 the difference in F6 should be highlighted in red if more than 7 hours
difference. Now if P was added in A6 then cell F6 should be highlighted blue
if the difference is more than 10 hours.
Clear as mud?

 

[Marty]

T.Valko, thanks for the advise, but your suggestion did not work

Anyone else help????

 

[T. Valko]

Use this formula for condition 1 (blue):

=AND(A6="P",ROUND((D6-C6)*24,0)>10)

Use this formula for condition 2 (red):

=AND(A6="W",ROUND((D6-C6)*24,0)>7)

 

[Marty]

Oh, so close:

W=Red,
for some reason the cell changes red at a difference of >7 hours 30 minutes
in cell F6

P=Blue,
for some reason the cell changes blue at a difference of >10 hours 30
minutes in cell F6

Thanks for the great advise so far.

 

[T. Valko]

Isn't that what you wanted?

F6 should be highlighted in red if more than 7 hours

F6 should be highlighted blue if the difference is
more than 10 hours.

 

[Marty]

In effect yes and thank you for helping thus far. What is happening is the
cells do not highlight at 7 and 10 hours, the highlighting only kicks in a 7
hours 30 min and 10 hours 30 min.
F6 is h:mm

 

[T. Valko]

Hmmm...

They both "kick in" when the hours *are greater than* 7 or 10. So, what you
actually want is that the format be applied when the hours *are greater than
or equal to* 7 or 10.

Ok, let's start from the beginning...

Select cell F6
Goto the menu Format>Conditional Formatting

Condition 1
Formula Is: =AND(A6="P",F6*24>=10)
Click the Format button
Select the Patterns tab
Select a nice shade of BLUE
OK

Click the Add button

Condition 2
Formula Is: =AND(A6="W",F6*24>=7)
Click the Format button
Select the Patterns tab
Select a nice shade of RED
OK out

 

[Marty]

Yahoo.. THANK YOU so much. It works

Hmmm...

They both "kick in" when the hours *are greater than* 7 or 10. So, what you
actually want is that the format be applied when the hours *are greater than
or equal to* 7 or 10.

Ok, let's start from the beginning...

Select cell F6
Goto the menu Format>Conditional Formatting

Condition 1
Formula Is: =AND(A6="P",F6*24>=10)
Click the Format button
Select the Patterns tab
Select a nice shade of BLUE
OK

Click the Add button

Condition 2
Formula Is: =AND(A6="W",F6*24>=7)
Click the Format button
Select the Patterns tab
Select a nice shade of RED
OK out

 

[T. Valko]

I'm glad we finally got this straightened out. Thanks for the feedback!