RYG Balls Based on Due Date and Status

MariaG

I would like to setup a formula with the following rules but am a novice and would appreciate help please!!!

Formula should work based on the following rules:

1) Red if Due Date is in the past and status is "In Progress" "Not Started" or "Requires Discussion"

2) Yellow if Due Date is in the next 7 days and status is "In Progress" "Not Started" or "Requires Discussion"

3) Green if Status is "Complete"

4) Grey if Due Date is blank or if status is "Not Using" or "Not Applicable"

Thank you all for being here and for your help! Happy New Year!

Paul Newcome

Try something like this...

=IF(OR(Status@row = "", Status@row = "Not Using", Status@row = "Not Applicable"), "Grey", IF(Status@row = "Complete", "Green", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow"))))

Maaik Meijerink

@MariaG I think this will do the trick. of course you would have to modify the formula if one must overrule the other: For example display Gray no matter what Status it is and Due Date is blank. In this case putting the Gray part in front instead of tin the end could do the tric:

=IF(AND([Due Date]@row < TODAY(); OR(Status@row = "In progress"; Status@row = "Not Started"; Status@row = "Requires Discussion")); "Red"; IF(AND([Due Date]@row < (TODAY() + 7); OR(Status@row = "In progress"; Status@row = "Not Started"; Status@row = "Requires Discussion")); "Yellow"; IF(Status@row = "Complete"; "Green"; IF(OR(NOT(ISDATE([Due Date]@row)); Status@row = "Not Using"; Status@row = "Not Applicable"); "Gray"))))

Paul Newcome

@Maaik Meijerink The order of the arguments will allow you to determine priority without having to include multiple AND/OR sets. You did however help me realize that I made a slight mistake in my formula. For Grey one of the conditions is that the Due Date is blank. I wrote it as if the Status were blank.

@MariaG Here is the corrected version:

=IF(OR([Due Date]@row = "", Status@row = "Not Using", Status@row = "Not Applicable"), "Grey", IF(Status@row = "Complete", "Green", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow"))))