Hi
I have the following formula =AND($A$6="w",$F$6TIME(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$6TIME(10,0,0))+$F$6 to the one
above but keep getting an error message.

Please help.

I wonder if I understand your question correctly.

=AND($A$6="w",$F$6TIME(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$6TIME(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.


--
Regards,

OssieMac


"Marty" wrote:

Hi
I have the following formula =AND($A$6="w",$F$6TIME(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$6TIME(10,0,0))+$F$6 to the one
above but keep getting an error message.

Please help.

=AND($A$6="w",$F$6TIME(7,0,0))+$F$6
=AND($A$6="p",$F$6TIME(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$6TIME(7,0,0))+$F$6

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

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


--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
Hi
I have the following formula =AND($A$6="w",$F$6TIME(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$6TIME(10,0,0))+$F$6 to the
one
above but keep getting an error message.

Please help.

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?


"T. Valko" wrote:

=AND($A$6="w",$F$6TIME(7,0,0))+$F$6
=AND($A$6="p",$F$6TIME(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$6TIME(7,0,0))+$F$6

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

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


--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
Hi
I have the following formula =AND($A$6="w",$F$6TIME(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$6TIME(10,0,0))+$F$6 to the
one
above but keep getting an error message.

Please help.

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

Anyone else help????

"Marty" wrote:

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?


"=AND($A$6="w",$F$6TIME(7,0,0))+$F$6+AND($A$6="p" ,$F$6TIME(10,0,0))+$F$6" wrote:

=AND($A$6="w",$F$6TIME(7,0,0))+$F$6
=AND($A$6="p",$F$6TIME(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$6TIME(7,0,0))+$F$6

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

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


--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
Hi
I have the following formula =AND($A$6="w",$F$6TIME(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$6TIME(10,0,0))+$F$6 to the
one
above but keep getting an error message.

Please help.

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)

--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
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?


"T. Valko" wrote:

=AND($A$6="w",$F$6TIME(7,0,0))+$F$6
=AND($A$6="p",$F$6TIME(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$6TIME(7,0,0))+$F$6

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

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


--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
Hi
I have the following formula =AND($A$6="w",$F$6TIME(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$6TIME(10,0,0))+$F$6 to
the
one
above but keep getting an error message.

Please help.

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
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?


"T. Valko" wrote:

=AND($A$6="w",$F$6TIME(7,0,0))+$F$6
=AND($A$6="p",$F$6TIME(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$6TIME(7,0,0))+$F$6

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

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


--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
Hi
I have the following formula =AND($A$6="w",$F$6TIME(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$6TIME(10,0,0))+$F$6 to
the
one
above but keep getting an error message.

Please help.

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.


--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
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?


"T. Valko" wrote:

=AND($A$6="w",$F$6TIME(7,0,0))+$F$6
=AND($A$6="p",$F$6TIME(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$6TIME(7,0,0))+$F$6

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

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


--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
Hi
I have the following formula =AND($A$6="w",$F$6TIME(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$6TIME(10,0,0))+$F$6
to
the
one
above but keep getting an error message.

Please help.