|
Add a formula to and existing formula
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. |
Add a formula to and existing formula
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. |
Add a formula to and existing formula
=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. |
Add a formula to and existing formula
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. |
Add a formula to and existing formula
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. |
Add a formula to and existing formula
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. |
Add a formula to and existing formula
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. |
Add a formula to and existing formula
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. |
Add a formula to and existing formula
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. |
Add a formula to and existing formula
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. |
Add a formula to and existing formula
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. |
Add a formula to and existing formula
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. |
| All times are GMT +1. The time now is 08:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com