Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLS HELP - Conditional Format w/Dates
I need help - I am trying to set up conditional formatting to show status
using the red, green and yellow "Stop Light". I have three conditions, but I am having troubles setting the formulas for them. 1. For the 1st condition, I want to show that if the action (Cell F2) is progressing beyond today, and is less than 45 days before the target completion date (Cell H2), then it is GREEN 2. For the 2nd, it would be if the action in cell F2 is greater than today, but within 45 days from the target completion date in Cell H2, then it would be yellow 3. For the 3rd, it the action in Cell F2 is equal to or greater than today, then it would be red. Someone please tell me how to set these formats - I could tell you what I have, but it would not be pretty - I need help for a school project. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLS HELP - Conditional Format w/Dates
Paste the following formula in the cell you want to format :
=IF(AND(F2<TODAY(),H2-F245),3,IF(AND(F2TODAY(),H2-F2<45),2,IF(F2=TODAY(),1,0))) In the conditional format, choose the spotlights, modify the rule : Green if value =3, yellow if value =2, else red. HTH Daniel I need help - I am trying to set up conditional formatting to show status using the red, green and yellow "Stop Light". I have three conditions, but I am having troubles setting the formulas for them. 1. For the 1st condition, I want to show that if the action (Cell F2) is progressing beyond today, and is less than 45 days before the target completion date (Cell H2), then it is GREEN 2. For the 2nd, it would be if the action in cell F2 is greater than today, but within 45 days from the target completion date in Cell H2, then it would be yellow 3. For the 3rd, it the action in Cell F2 is equal to or greater than today, then it would be red. Someone please tell me how to set these formats - I could tell you what I have, but it would not be pretty - I need help for a school project. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLS HELP - Conditional Format w/Dates
Hi Jacki,
Conditional formatting is ok. I can give you the process and the formula but the issue is the today's date. Now after we have put the conditional format for today, the same excel sheet may change when you open it tomorrow as the current date changes. Is it ok for you? or do you want to mention any specific date for reference. -- Kind Regards, Satti Charvak Only an Excel Enthusiast Noida, India "Jacki" wrote: I need help - I am trying to set up conditional formatting to show status using the red, green and yellow "Stop Light". I have three conditions, but I am having troubles setting the formulas for them. 1. For the 1st condition, I want to show that if the action (Cell F2) is progressing beyond today, and is less than 45 days before the target completion date (Cell H2), then it is GREEN 2. For the 2nd, it would be if the action in cell F2 is greater than today, but within 45 days from the target completion date in Cell H2, then it would be yellow 3. For the 3rd, it the action in Cell F2 is equal to or greater than today, then it would be red. Someone please tell me how to set these formats - I could tell you what I have, but it would not be pretty - I need help for a school project. Thanks! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLS HELP - Conditional Format w/Dates
Hi Satti:
I guess it would be ok to use TODAY as the formula, so that it becomes the factor by which my formula will know if the action is within 45 days, overdue. etc. I'd appreciate any help you can give me. Thanks "Satti Charvak" wrote: Hi Jacki, Conditional formatting is ok. I can give you the process and the formula but the issue is the today's date. Now after we have put the conditional format for today, the same excel sheet may change when you open it tomorrow as the current date changes. Is it ok for you? or do you want to mention any specific date for reference. -- Kind Regards, Satti Charvak Only an Excel Enthusiast Noida, India "Jacki" wrote: I need help - I am trying to set up conditional formatting to show status using the red, green and yellow "Stop Light". I have three conditions, but I am having troubles setting the formulas for them. 1. For the 1st condition, I want to show that if the action (Cell F2) is progressing beyond today, and is less than 45 days before the target completion date (Cell H2), then it is GREEN 2. For the 2nd, it would be if the action in cell F2 is greater than today, but within 45 days from the target completion date in Cell H2, then it would be yellow 3. For the 3rd, it the action in Cell F2 is equal to or greater than today, then it would be red. Someone please tell me how to set these formats - I could tell you what I have, but it would not be pretty - I need help for a school project. Thanks! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLS HELP - Conditional Format w/Dates
Daniel,
Thank you for your reply and your help- and please forgive my ignorance: is this whole formula pasted into one condition, or is this the formula for all three conditions? If so, where is the break for the other conditions? Please advise, and in the meantime, I'm going to try to work it. "Daniel.C" wrote: Paste the following formula in the cell you want to format : =IF(AND(F2<TODAY(),H2-F245),3,IF(AND(F2TODAY(),H2-F2<45),2,IF(F2=TODAY(),1,0))) In the conditional format, choose the spotlights, modify the rule : Green if value =3, yellow if value =2, else red. HTH Daniel I need help - I am trying to set up conditional formatting to show status using the red, green and yellow "Stop Light". I have three conditions, but I am having troubles setting the formulas for them. 1. For the 1st condition, I want to show that if the action (Cell F2) is progressing beyond today, and is less than 45 days before the target completion date (Cell H2), then it is GREEN 2. For the 2nd, it would be if the action in cell F2 is greater than today, but within 45 days from the target completion date in Cell H2, then it would be yellow 3. For the 3rd, it the action in Cell F2 is equal to or greater than today, then it would be red. Someone please tell me how to set these formats - I could tell you what I have, but it would not be pretty - I need help for a school project. Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLS HELP - Conditional Format w/Dates
I assumed you wanted to use the stop lights of XL2007 conditional
format. If it's not the case, please forget my answer. This formatting is based on the values of the cell, so you have to enter the formula in the cell to which you want to apply a conditional formatting. Daniel Daniel, Thank you for your reply and your help- and please forgive my ignorance: is this whole formula pasted into one condition, or is this the formula for all three conditions? If so, where is the break for the other conditions? Please advise, and in the meantime, I'm going to try to work it. "Daniel.C" wrote: Paste the following formula in the cell you want to format : =IF(AND(F2<TODAY(),H2-F245),3,IF(AND(F2TODAY(),H2-F2<45),2,IF(F2=TODAY(),1,0))) In the conditional format, choose the spotlights, modify the rule : Green if value =3, yellow if value =2, else red. HTH Daniel I need help - I am trying to set up conditional formatting to show status using the red, green and yellow "Stop Light". I have three conditions, but I am having troubles setting the formulas for them. 1. For the 1st condition, I want to show that if the action (Cell F2) is progressing beyond today, and is less than 45 days before the target completion date (Cell H2), then it is GREEN 2. For the 2nd, it would be if the action in cell F2 is greater than today, but within 45 days from the target completion date in Cell H2, then it would be yellow 3. For the 3rd, it the action in Cell F2 is equal to or greater than today, then it would be red. Someone please tell me how to set these formats - I could tell you what I have, but it would not be pretty - I need help for a school project. Thanks! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLS HELP - Conditional Format w/Dates
No Daniel, actually, I am using Excel 2003. Thanks anyway Daniel - I do
appreciate your efforts. Jacki "Daniel.C" wrote: I assumed you wanted to use the stop lights of XL2007 conditional format. If it's not the case, please forget my answer. This formatting is based on the values of the cell, so you have to enter the formula in the cell to which you want to apply a conditional formatting. Daniel Daniel, Thank you for your reply and your help- and please forgive my ignorance: is this whole formula pasted into one condition, or is this the formula for all three conditions? If so, where is the break for the other conditions? Please advise, and in the meantime, I'm going to try to work it. "Daniel.C" wrote: Paste the following formula in the cell you want to format : =IF(AND(F2<TODAY(),H2-F245),3,IF(AND(F2TODAY(),H2-F2<45),2,IF(F2=TODAY(),1,0))) In the conditional format, choose the spotlights, modify the rule : Green if value =3, yellow if value =2, else red. HTH Daniel I need help - I am trying to set up conditional formatting to show status using the red, green and yellow "Stop Light". I have three conditions, but I am having troubles setting the formulas for them. 1. For the 1st condition, I want to show that if the action (Cell F2) is progressing beyond today, and is less than 45 days before the target completion date (Cell H2), then it is GREEN 2. For the 2nd, it would be if the action in cell F2 is greater than today, but within 45 days from the target completion date in Cell H2, then it would be yellow 3. For the 3rd, it the action in Cell F2 is equal to or greater than today, then it would be red. Someone please tell me how to set these formats - I could tell you what I have, but it would not be pretty - I need help for a school project. Thanks! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
PLS HELP - Conditional Format w/Dates
Then use the 3 formulae :
=AND(F2<TODAY(),H2-F245) (red) =AND(F2TODAY(),H2-F2<45) (yellow) =F2=TODAY() (green) Note that the 2 last ones may be both true. You'll have to decide which none to apply in that case, or use : =AND(F2TODAY(),H2-F2=45) (green) for the third case. Daniel No Daniel, actually, I am using Excel 2003. Thanks anyway Daniel - I do appreciate your efforts. Jacki "Daniel.C" wrote: I assumed you wanted to use the stop lights of XL2007 conditional format. If it's not the case, please forget my answer. This formatting is based on the values of the cell, so you have to enter the formula in the cell to which you want to apply a conditional formatting. Daniel Daniel, Thank you for your reply and your help- and please forgive my ignorance: is this whole formula pasted into one condition, or is this the formula for all three conditions? If so, where is the break for the other conditions? Please advise, and in the meantime, I'm going to try to work it. "Daniel.C" wrote: Paste the following formula in the cell you want to format : =IF(AND(F2<TODAY(),H2-F245),3,IF(AND(F2TODAY(),H2-F2<45),2,IF(F2=TODAY(),1,0))) In the conditional format, choose the spotlights, modify the rule : Green if value =3, yellow if value =2, else red. HTH Daniel I need help - I am trying to set up conditional formatting to show status using the red, green and yellow "Stop Light". I have three conditions, but I am having troubles setting the formulas for them. 1. For the 1st condition, I want to show that if the action (Cell F2) is progressing beyond today, and is less than 45 days before the target completion date (Cell H2), then it is GREEN 2. For the 2nd, it would be if the action in cell F2 is greater than today, but within 45 days from the target completion date in Cell H2, then it would be yellow 3. For the 3rd, it the action in Cell F2 is equal to or greater than today, then it would be red. Someone please tell me how to set these formats - I could tell you what I have, but it would not be pretty - I need help for a school project. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Format with dates | Excel Discussion (Misc queries) | |||
Using Conditional Format/Icon Set With Dates | Excel Worksheet Functions | |||
Conditional Format - Due Dates - 2 questions (2nd posting) | Excel Discussion (Misc queries) | |||
Conditional Format - Due Dates - 2 questions | Excel Discussion (Misc queries) | |||
Conditional Format matching Dates,calendar | Excel Discussion (Misc queries) |