What formula to use to show how status changes depending on days prior to due date

Options
edited 06/22/21

What formula would I use to show the following:

• Project not started 20+ days prior to due date, status = green
• Project not started 15-19 days prior to due date, status = yellow
• Project not started < 15 days prior to due date, status = red

• ✭✭✭✭✭✭
Options

Hi @Profe17

Hope you are fine, please try the following formula and convert it to a column format formula:

=IFERROR(IF(TODAY() - [due date]@row < 15, "Red", IF(TODAY() - [due date]@row > 20, "Green", "Yellow")), "")

the following screenshots shows the result:

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Hi @Profe17

Hope you are fine, Please try the following formula:

=IF(AND(TODAY() - [due date]@row < 20, TODAY() - [due date]@row > 15, Status@row = "not started"), "Yellow", IF(AND(TODAY() - [due date]@row < 15, Status@row = "not started"), "Red", IF(AND(TODAY() - [due date]@row > 20, Status@row = "not started"), "Green", "")))

1. and for your question " How can I add in absolute value to the above formula?" use the function ABS Function

bassam.khalil2009@gmail.com

• ✭✭✭✭✭✭
Options

Hi @Profe17

Hope you are fine, please try the following formula and convert it to a column format formula:

=IFERROR(IF(TODAY() - [due date]@row < 15, "Red", IF(TODAY() - [due date]@row > 20, "Green", "Yellow")), "")

the following screenshots shows the result:

bassam.khalil2009@gmail.com

• Options

Thank you! That worked!

• Options

This formula worked, but I now need to bring in more information and i'm wondering if that is possible.

=IFERROR(IF(TODAY() - [due date]@row < 15, "Red", IF(TODAY() - [due date]@row > 20, "Green", "Yellow")), "")

My question is two parts.

1. How can I add in absolute value to the above formula?
2. Can I add in another variable that would include status? Please see below
1. Today is 15-19 days prior to due date and status = "not started" so health is "Yellow"
2. Today is < 15 days prior to due date and status = "not started" so health is "red"
3. Today is 20+ days prior to due date and status = "not started" so health is "green"
• ✭✭✭✭✭✭
Options

Hi @Profe17

Hope you are fine, Please try the following formula:

=IF(AND(TODAY() - [due date]@row < 20, TODAY() - [due date]@row > 15, Status@row = "not started"), "Yellow", IF(AND(TODAY() - [due date]@row < 15, Status@row = "not started"), "Red", IF(AND(TODAY() - [due date]@row > 20, Status@row = "not started"), "Green", "")))

1. and for your question " How can I add in absolute value to the above formula?" use the function ABS Function

bassam.khalil2009@gmail.com

• Options

That worked perfectly! Thanks again!

• ✭✭✭✭✭✭
Options

You are welcome and I will be happy to help you any time.

bassam.khalil2009@gmail.com