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

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

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

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