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

Theresa Bruns

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

Bassam Khalil

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 Khalil

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 Khalil

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:

Theresa Bruns

Thank you! That worked!

Theresa Bruns

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"

Bassam Khalil

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

Theresa Bruns

That worked perfectly! Thanks again!

Bassam Khalil

@Profe17

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