RYG Ball based on due date

Options

Hi

Can someone please help us with a formula to get:

  • If the Due date is 8 days or prior to, our payment priority is Green.
  • If the Due date is between 0 and 7 days, our payment priority is Yellow.
  • If the Due date exceeds, our payment priority is Red.

Thank you very much in advance.

Best Answer

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @BRoy

    You can use a combo of IF statements and AND operators:

    =IF([Due Date]@row > TODAY(7), "Green", IF(AND([Due Date]@row <= TODAY(7), [Due Date]@row >= TODAY()), "Yellow", "Red"))

    Note: this formula assumes you meant prior to today. If not, just flip the colors signs around.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Andrea Hunter
    Options

    @Ryan Sides

    Thanks for your help. We tried the IF statement you provided and swapped the "Green" and "Red".

    =IF([Due Date]@row > TODAY(), "Red", IF(AND([Due Date]@row <= TODAY(7), [Due Date]@row >= TODAY()), "Yellow", "Green"))

    As for the "Yellow" it is only appearing when the due date is today's date. Do you know how we can make it "Yellow" when it is between 0 and 7 days (before the due date)?

    Thank you in advance for your assistance :)

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    Sure thing!

    =IF([Due Date]@row > TODAY(), "Red", IF([Due Date]@row >= TODAY(-7), "Yellow", "Green"))

    Like that?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Andrea Hunter
    Options

    @Ryan Sides I believe this is the one we were looking for. Thank you so much!😀

  • Andrea Hunter
    Options

    Hoping someone can help assist me.

    As per previous inquiry above (thank you @Ryan Sides), this is what is in place under column "Payment Status/Inquiry" (this part is good)

    =IF([Due Date]@row > TODAY(), "Green", IF([Due Date]@row >= TODAY(-7), "Yellow", "Red"))

    In another column, we have a check box "Invoice Paid"

    Do you know how you can freeze time (the RYG from changing) once the check box has been checked?

    Thanks in advance.

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @Andrea Hunter You can't freeze it in the same column as the original RYG. You'll need an automation that triggers when the check box is checked to record the color in another column.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!