If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting Based on Due Dates
I want a stop light color to change from green, yellow, or red based on a
specific number of days prior to the due date. For example: If A1 is the stoplight color: 1) I want B8 green if the date in AE8 or AF8 is greater than 35 days prior to the date in the field. 2) I want B8 red if the date in AE8 or AF8 is less than or equal to 35 days prior to the date in the field. 3) I want B8 yellow if the date in AM8 or AN8 has passed. And I need to be able to replicate this for each record. Thanks! Jacob |
#2
|
|||
|
|||
Conditional Formatting Based on Due Dates
Not sure if your last condition was meant to read AE & AF, or if you really
meant AM & AN (possible typo?). As you said it, should setup this way: format - conditional format Condition 1, formula is: =OR(TODAY()AM8,TODAY()AN8) Choose yellow pattern Condition 2, formula is: =OR(AE8B8-35,AF8B8-35) Choose green pattern Condition 3, formula is: =OR(AE8=B8-35,AF8=B8-35) Choose red pattern You should be able to copy this format to other cells as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Morgimo" wrote: I want a stop light color to change from green, yellow, or red based on a specific number of days prior to the due date. For example: If A1 is the stoplight color: 1) I want B8 green if the date in AE8 or AF8 is greater than 35 days prior to the date in the field. 2) I want B8 red if the date in AE8 or AF8 is less than or equal to 35 days prior to the date in the field. 3) I want B8 yellow if the date in AM8 or AN8 has passed. And I need to be able to replicate this for each record. Thanks! Jacob |
#3
|
|||
|
|||
Conditional Formatting Based on Due Dates
I did mean AM & AN, but I also gave you some incorrect info...my bad! B8 does
not have a date, it's only a color. The 35 days greater than or less than is based on the date in AE8 or AF8. And yellow happens when AM8 is greater than AN8. So will this work.... B8 = GREEN if AE8 or AF8 is greater than 35 days before due date =OR(AE8AE8-35,AF8AF8-35) B8 = RED if AE8 or AF8 is less than or equal to 35 days before due date =OR(AE8=AE8-35,AF8=AF8-35) B8 = YELLOW if AM8 is greater than AN8 =OR(AM8AN8) Thanks, Jacob "Luke M" wrote: Not sure if your last condition was meant to read AE & AF, or if you really meant AM & AN (possible typo?). As you said it, should setup this way: format - conditional format Condition 1, formula is: =OR(TODAY()AM8,TODAY()AN8) Choose yellow pattern Condition 2, formula is: =OR(AE8B8-35,AF8B8-35) Choose green pattern Condition 3, formula is: =OR(AE8=B8-35,AF8=B8-35) Choose red pattern You should be able to copy this format to other cells as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Morgimo" wrote: I want a stop light color to change from green, yellow, or red based on a specific number of days prior to the due date. For example: If A1 is the stoplight color: 1) I want B8 green if the date in AE8 or AF8 is greater than 35 days prior to the date in the field. 2) I want B8 red if the date in AE8 or AF8 is less than or equal to 35 days prior to the date in the field. 3) I want B8 yellow if the date in AM8 or AN8 has passed. And I need to be able to replicate this for each record. Thanks! Jacob |
#4
|
|||
|
|||
Conditional Formatting Based on Due Dates
Looking at what I did it looks circular.I need it to know when today it is 35
days before the value in the cell or if today is within 35 days. "Morgimo" wrote: I did mean AM & AN, but I also gave you some incorrect info...my bad! B8 does not have a date, it's only a color. The 35 days greater than or less than is based on the date in AE8 or AF8. And yellow happens when AM8 is greater than AN8. So will this work.... B8 = GREEN if AE8 or AF8 is greater than 35 days before due date =OR(AE8AE8-35,AF8AF8-35) B8 = RED if AE8 or AF8 is less than or equal to 35 days before due date =OR(AE8=AE8-35,AF8=AF8-35) B8 = YELLOW if AM8 is greater than AN8 =OR(AM8AN8) Thanks, Jacob "Luke M" wrote: Not sure if your last condition was meant to read AE & AF, or if you really meant AM & AN (possible typo?). As you said it, should setup this way: format - conditional format Condition 1, formula is: =OR(TODAY()AM8,TODAY()AN8) Choose yellow pattern Condition 2, formula is: =OR(AE8B8-35,AF8B8-35) Choose green pattern Condition 3, formula is: =OR(AE8=B8-35,AF8=B8-35) Choose red pattern You should be able to copy this format to other cells as needed. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Morgimo" wrote: I want a stop light color to change from green, yellow, or red based on a specific number of days prior to the due date. For example: If A1 is the stoplight color: 1) I want B8 green if the date in AE8 or AF8 is greater than 35 days prior to the date in the field. 2) I want B8 red if the date in AE8 or AF8 is less than or equal to 35 days prior to the date in the field. 3) I want B8 yellow if the date in AM8 or AN8 has passed. And I need to be able to replicate this for each record. Thanks! Jacob |
Thread Tools | |
Display Modes | |
|
|