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

Theresa Bruns
Theresa Bruns ✭✭
edited 06/22/21 in Formulas and Functions

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


Best Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Thank you! That worked!

  • 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
    Bassam Khalil ✭✭✭✭✭✭
    Answer ✓

    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


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • That worked perfectly! Thanks again!

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭

    @Profe17

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!