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.

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

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.

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 FormatConditional 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


--
Biff
Microsoft Excel MVP


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

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.

Yahoo.. THANK YOU so much. It works

"T. Valko" wrote:

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 FormatConditional 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


--
Biff
Microsoft Excel MVP


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

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.

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

--
Biff
Microsoft Excel MVP


"Marty" wrote in message
...
Yahoo.. THANK YOU so much. It works

"T. Valko" wrote:

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 FormatConditional 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


--
Biff
Microsoft Excel MVP


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

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.