Automate RYG Balls based on Status and then date

apmarcotte

Does anyone have a formula that looks at a line status (Not Started, In Progress, Complete) and then determines whether it is after the due date (red), <7 days (yellow) , or >7 days (green)?

Complete would need to turn green regardless of the date while not started and in progress would look at the date parameters.

Thanks in advanced as I am new to Smartsheet and writing these formulas!

 

Dominik Weller

Hi apmarcotte,

you can use the following formula that should give you the result you need. For your sheet you need to rename the coloums ([Line Status], [Due Date]) in the formula:

=IF([Line Status]1 = "Complete"; "green"; IF([Due Date]1 - TODAY() < 0; "red"; IF([Due Date]1 - TODAY() < 7; "yellow"; "green")))

Rows with empty dates will be shown as yellow, if you want them to be red, you should use:

=IF([Line Status]1 = "Complete"; "green"; IF(ISBLANK([Due Date]1); "red"; IF([Due Date]1 - TODAY() < 0; "red"; IF([Due Date]1 - TODAY() < 7; "yellow"; "green"))))

Greetings

PS: Maybe you have to exchange all semicolons ; with commas , ... Since I use german language settings, this may not work for you.

apmarcotte

Thanks Dominik,

This worked great. 

LaurieCass

@Dominik Weller Thank You!! I was struggling to write this formula, so glad I came across this thread.