RYG Ball based on due date

BRoy

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.

Ryan Sides

Sure thing!

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

Like that?

Ryan Sides

@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.

Andrea Hunter

@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

Sure thing!

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

Like that?

Andrea Hunter

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

Andrea Hunter

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

@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.