| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
More Help with Conditional Formatting
I now have it sort of working with this formula:
=IF(AND(L7<(A1)-L760),"Pending",IF(AND(L7(A1)-L7<180),"FollowUp",IF(L7=(A1),"Completed",0))) My problem now is that it isn't calculating the dates correctly it is showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it is still showing as "pending" instead of "completed"). I also would like to see if I could use the "stop light" coloring with this as well and have yellow for the pending section, red for followup and green for completed, if possible. -- Thank you! Shelly |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More Help with Conditional Formatting
I don't think your formula is written for what you actually want. When you
use the AND, you're wanting both conditions? You currently have it setup as one condition. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) Written this way, if L7 is less than A1 AND L7 is greater than 60, its "Pending". For the stoplight, Format-Conditional formatting condition1 Set cell value equal to pending, format yellow, condition2 set cell value equal to follow-up, format red condition3 set cell value equal to completed, format green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: I now have it sort of working with this formula: =IF(AND(L7<(A1)-L760),"Pending",IF(AND(L7(A1)-L7<180),"FollowUp",IF(L7=(A1),"Completed",0))) My problem now is that it isn't calculating the dates correctly it is showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it is still showing as "pending" instead of "completed"). I also would like to see if I could use the "stop light" coloring with this as well and have yellow for the pending section, red for followup and green for completed, if possible. -- Thank you! Shelly |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More Help with Conditional Formatting
How would the formula work for the color coding?
You're right, I don't want the AND condition to do that. What are your suggestions? -- Thank you! Shelly "Luke M" wrote: I don't think your formula is written for what you actually want. When you use the AND, you're wanting both conditions? You currently have it setup as one condition. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) Written this way, if L7 is less than A1 AND L7 is greater than 60, its "Pending". For the stoplight, Format-Conditional formatting condition1 Set cell value equal to pending, format yellow, condition2 set cell value equal to follow-up, format red condition3 set cell value equal to completed, format green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: I now have it sort of working with this formula: =IF(AND(L7<(A1)-L760),"Pending",IF(AND(L7(A1)-L7<180),"FollowUp",IF(L7=(A1),"Completed",0))) My problem now is that it isn't calculating the dates correctly it is showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it is still showing as "pending" instead of "completed"). I also would like to see if I could use the "stop light" coloring with this as well and have yellow for the pending section, red for followup and green for completed, if possible. -- Thank you! Shelly |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More Help with Conditional Formatting
The formula I wrote is the one you should be able to use in your cell.
=IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) For the color coding, you don't need a formula. Go to Format - Conditional Format. It should be set to cell value, equals, and then type "Pending" (sans quotes) Then click format, patterns, and pick color you want. Add additional conditions for the other two outcomes. If the formula I gave you is not what you want, please write out what conditions you are desiring for clarification. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: How would the formula work for the color coding? You're right, I don't want the AND condition to do that. What are your suggestions? -- Thank you! Shelly "Luke M" wrote: I don't think your formula is written for what you actually want. When you use the AND, you're wanting both conditions? You currently have it setup as one condition. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) Written this way, if L7 is less than A1 AND L7 is greater than 60, its "Pending". For the stoplight, Format-Conditional formatting condition1 Set cell value equal to pending, format yellow, condition2 set cell value equal to follow-up, format red condition3 set cell value equal to completed, format green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: I now have it sort of working with this formula: =IF(AND(L7<(A1)-L760),"Pending",IF(AND(L7(A1)-L7<180),"FollowUp",IF(L7=(A1),"Completed",0))) My problem now is that it isn't calculating the dates correctly it is showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it is still showing as "pending" instead of "completed"). I also would like to see if I could use the "stop light" coloring with this as well and have yellow for the pending section, red for followup and green for completed, if possible. -- Thank you! Shelly |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More Help with Conditional Formatting
Thank you, This new formula works except now instead of showing "pending",
"followup", etc., it says TRUE and FALSE....how do we fix this? -- Thank you! Shelly "Luke M" wrote: The formula I wrote is the one you should be able to use in your cell. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) For the color coding, you don't need a formula. Go to Format - Conditional Format. It should be set to cell value, equals, and then type "Pending" (sans quotes) Then click format, patterns, and pick color you want. Add additional conditions for the other two outcomes. If the formula I gave you is not what you want, please write out what conditions you are desiring for clarification. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: How would the formula work for the color coding? You're right, I don't want the AND condition to do that. What are your suggestions? -- Thank you! Shelly "Luke M" wrote: I don't think your formula is written for what you actually want. When you use the AND, you're wanting both conditions? You currently have it setup as one condition. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) Written this way, if L7 is less than A1 AND L7 is greater than 60, its "Pending". For the stoplight, Format-Conditional formatting condition1 Set cell value equal to pending, format yellow, condition2 set cell value equal to follow-up, format red condition3 set cell value equal to completed, format green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: I now have it sort of working with this formula: =IF(AND(L7<(A1)-L760),"Pending",IF(AND(L7(A1)-L7<180),"FollowUp",IF(L7=(A1),"Completed",0))) My problem now is that it isn't calculating the dates correctly it is showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it is still showing as "pending" instead of "completed"). I also would like to see if I could use the "stop light" coloring with this as well and have yellow for the pending section, red for followup and green for completed, if possible. -- Thank you! Shelly |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More Help with Conditional Formatting
You fix it by using the formula as suggested.
=IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) It can't give TRUE or FALSE. -- David Biddulph "Shelly" wrote in message ... Thank you, This new formula works except now instead of showing "pending", "followup", etc., it says TRUE and FALSE....how do we fix this? -- Thank you! Shelly "Luke M" wrote: The formula I wrote is the one you should be able to use in your cell. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) For the color coding, you don't need a formula. Go to Format - Conditional Format. It should be set to cell value, equals, and then type "Pending" (sans quotes) Then click format, patterns, and pick color you want. Add additional conditions for the other two outcomes. If the formula I gave you is not what you want, please write out what conditions you are desiring for clarification. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: How would the formula work for the color coding? You're right, I don't want the AND condition to do that. What are your suggestions? -- Thank you! Shelly "Luke M" wrote: I don't think your formula is written for what you actually want. When you use the AND, you're wanting both conditions? You currently have it setup as one condition. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) Written this way, if L7 is less than A1 AND L7 is greater than 60, its "Pending". For the stoplight, Format-Conditional formatting condition1 Set cell value equal to pending, format yellow, condition2 set cell value equal to follow-up, format red condition3 set cell value equal to completed, format green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: I now have it sort of working with this formula: =IF(AND(L7<(A1)-L760),"Pending",IF(AND(L7(A1)-L7<180),"FollowUp",IF(L7=(A1),"Completed",0))) My problem now is that it isn't calculating the dates correctly it is showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it is still showing as "pending" instead of "completed"). I also would like to see if I could use the "stop light" coloring with this as well and have yellow for the pending section, red for followup and green for completed, if possible. -- Thank you! Shelly |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More Help with Conditional Formatting
It did, I fixed it, now it's back to saying "pending". But it still isn't
doing what I want. I want it to say "pending" if the time between when I tasked something (cell M7) is 60 days or less than the current date (A1); "followup" for items between 60 and 180 days; and "completed" from 180+ If this helps any. I really would appreciate any help for this! -- Thank you! Shelly "David Biddulph" wrote: You fix it by using the formula as suggested. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) It can't give TRUE or FALSE. -- David Biddulph "Shelly" wrote in message ... Thank you, This new formula works except now instead of showing "pending", "followup", etc., it says TRUE and FALSE....how do we fix this? -- Thank you! Shelly "Luke M" wrote: The formula I wrote is the one you should be able to use in your cell. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) For the color coding, you don't need a formula. Go to Format - Conditional Format. It should be set to cell value, equals, and then type "Pending" (sans quotes) Then click format, patterns, and pick color you want. Add additional conditions for the other two outcomes. If the formula I gave you is not what you want, please write out what conditions you are desiring for clarification. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: How would the formula work for the color coding? You're right, I don't want the AND condition to do that. What are your suggestions? -- Thank you! Shelly "Luke M" wrote: I don't think your formula is written for what you actually want. When you use the AND, you're wanting both conditions? You currently have it setup as one condition. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) Written this way, if L7 is less than A1 AND L7 is greater than 60, its "Pending". For the stoplight, Format-Conditional formatting condition1 Set cell value equal to pending, format yellow, condition2 set cell value equal to follow-up, format red condition3 set cell value equal to completed, format green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: I now have it sort of working with this formula: =IF(AND(L7<(A1)-L760),"Pending",IF(AND(L7(A1)-L7<180),"FollowUp",IF(L7=(A1),"Completed",0))) My problem now is that it isn't calculating the dates correctly it is showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it is still showing as "pending" instead of "completed"). I also would like to see if I could use the "stop light" coloring with this as well and have yellow for the pending section, red for followup and green for completed, if possible. -- Thank you! Shelly |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More Help with Conditional Formatting
Well, you can see the syntax. Change the terms to suit your requirements.
-- David Biddulph "Shelly" wrote in message ... It did, I fixed it, now it's back to saying "pending". But it still isn't doing what I want. I want it to say "pending" if the time between when I tasked something (cell M7) is 60 days or less than the current date (A1); "followup" for items between 60 and 180 days; and "completed" from 180+ If this helps any. I really would appreciate any help for this! -- Thank you! Shelly "David Biddulph" wrote: You fix it by using the formula as suggested. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) It can't give TRUE or FALSE. -- David Biddulph "Shelly" wrote in message ... Thank you, This new formula works except now instead of showing "pending", "followup", etc., it says TRUE and FALSE....how do we fix this? -- Thank you! Shelly "Luke M" wrote: The formula I wrote is the one you should be able to use in your cell. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) For the color coding, you don't need a formula. Go to Format - Conditional Format. It should be set to cell value, equals, and then type "Pending" (sans quotes) Then click format, patterns, and pick color you want. Add additional conditions for the other two outcomes. If the formula I gave you is not what you want, please write out what conditions you are desiring for clarification. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: How would the formula work for the color coding? You're right, I don't want the AND condition to do that. What are your suggestions? -- Thank you! Shelly "Luke M" wrote: I don't think your formula is written for what you actually want. When you use the AND, you're wanting both conditions? You currently have it setup as one condition. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) Written this way, if L7 is less than A1 AND L7 is greater than 60, its "Pending". For the stoplight, Format-Conditional formatting condition1 Set cell value equal to pending, format yellow, condition2 set cell value equal to follow-up, format red condition3 set cell value equal to completed, format green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: I now have it sort of working with this formula: =IF(AND(L7<(A1)-L760),"Pending",IF(AND(L7(A1)-L7<180),"FollowUp",IF(L7=(A1),"Completed",0))) My problem now is that it isn't calculating the dates correctly it is showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it is still showing as "pending" instead of "completed"). I also would like to see if I could use the "stop light" coloring with this as well and have yellow for the pending section, red for followup and green for completed, if possible. -- Thank you! Shelly |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
More Help with Conditional Formatting
I'm sorry, but I'm not sure if I understand what you mean. Is there a
particular formula I should use? -- Thank you! Shelly "David Biddulph" wrote: Well, you can see the syntax. Change the terms to suit your requirements. -- David Biddulph "Shelly" wrote in message ... It did, I fixed it, now it's back to saying "pending". But it still isn't doing what I want. I want it to say "pending" if the time between when I tasked something (cell M7) is 60 days or less than the current date (A1); "followup" for items between 60 and 180 days; and "completed" from 180+ If this helps any. I really would appreciate any help for this! -- Thank you! Shelly "David Biddulph" wrote: You fix it by using the formula as suggested. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) It can't give TRUE or FALSE. -- David Biddulph "Shelly" wrote in message ... Thank you, This new formula works except now instead of showing "pending", "followup", etc., it says TRUE and FALSE....how do we fix this? -- Thank you! Shelly "Luke M" wrote: The formula I wrote is the one you should be able to use in your cell. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) For the color coding, you don't need a formula. Go to Format - Conditional Format. It should be set to cell value, equals, and then type "Pending" (sans quotes) Then click format, patterns, and pick color you want. Add additional conditions for the other two outcomes. If the formula I gave you is not what you want, please write out what conditions you are desiring for clarification. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: How would the formula work for the color coding? You're right, I don't want the AND condition to do that. What are your suggestions? -- Thank you! Shelly "Luke M" wrote: I don't think your formula is written for what you actually want. When you use the AND, you're wanting both conditions? You currently have it setup as one condition. =IF(AND(L7<(A1),L760),"Pending",IF(AND(L7(A1),L7 <180),"FollowUp",IF(L7=(A1),"Completed",0))) Written this way, if L7 is less than A1 AND L7 is greater than 60, its "Pending". For the stoplight, Format-Conditional formatting condition1 Set cell value equal to pending, format yellow, condition2 set cell value equal to follow-up, format red condition3 set cell value equal to completed, format green -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Shelly" wrote: I now have it sort of working with this formula: =IF(AND(L7<(A1)-L760),"Pending",IF(AND(L7(A1)-L7<180),"FollowUp",IF(L7=(A1),"Completed",0))) My problem now is that it isn't calculating the dates correctly it is showing all cells as being "pending" (i.e. when I put in the date 1/2/2006 it is still showing as "pending" instead of "completed"). I also would like to see if I could use the "stop light" coloring with this as well and have yellow for the pending section, red for followup and green for completed, if possible. -- Thank you! Shelly |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
| Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
| Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Linear Mode
