Automatic RYGB Lights

Jeffrey Wagemans

Hi All,

Currently I'm trying to make my RYGB column give the right color light when it meets certain criteria. This must be based on a Start Date column, End Date column and a status column, but I can't seem to figure out a formula that works.

So the way it should work is like below:

Is there anyone who might know a solution? 😃

Thanks in advance!

Emilio Wright

@Jeffrey Wagemans I created the formula based on your table, although it has gaps and you will begin to notice them when users begin to use the fields. I found one but have not changed the formula since you provided the needed information.

=IF(Status@row = "not started", IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "Blue", IF([Start Date]@row > TODAY(), "Blue", "Red")), IF(Status@row = "in progress", IF([End Date]@row < TODAY(), "Red", "Yellow"), IF(AND(Status@row = "Completed", [End Date]@row < TODAY()), "Green", IF(Status@row = "Canceled", "Blue", "Blue"))))

I created a formula similar to this on my Project Plans. I opted by not caring what the user had on the Status field unless it was something critical like "At Risk" or "Abandoned" or "Completed". I simply used the Date fields to guide my Status. You can kind of gauge what status it should be based on the dates.

This is a snippet of the formula but I think it conveys the message. Don't mind the parenthesis as I simply cut this from my formula without checking if the parentheses have pairs.

IF(AND(NOT(ISBLANK([Start Date]#)), NOT(ISBLANK([End Date]#))), IF(AND([Start Date]# <= TODAY(), [End Date]# >= TODAY()), "In Progress", IF(AND([Start Date]# <= TODAY(), [End Date]# < TODAY()), "Completed", "Planned Not Started")), "Unplanned"))

Emilio Wright

@Jeffrey Wagemans I can help you make the formula, it would be quite a few nested IF statements. Normally I would take the column that has the most options per group as the initial criteria since it will result in fewer IF statements.

Emilio Wright

@Jeffrey Wagemans I created the formula based on your table, although it has gaps and you will begin to notice them when users begin to use the fields. I found one but have not changed the formula since you provided the needed information.

=IF(Status@row = "not started", IF(OR(ISBLANK([Start Date]@row), ISBLANK([End Date]@row)), "Blue", IF([Start Date]@row > TODAY(), "Blue", "Red")), IF(Status@row = "in progress", IF([End Date]@row < TODAY(), "Red", "Yellow"), IF(AND(Status@row = "Completed", [End Date]@row < TODAY()), "Green", IF(Status@row = "Canceled", "Blue", "Blue"))))

I created a formula similar to this on my Project Plans. I opted by not caring what the user had on the Status field unless it was something critical like "At Risk" or "Abandoned" or "Completed". I simply used the Date fields to guide my Status. You can kind of gauge what status it should be based on the dates.

This is a snippet of the formula but I think it conveys the message. Don't mind the parenthesis as I simply cut this from my formula without checking if the parentheses have pairs.

IF(AND(NOT(ISBLANK([Start Date]#)), NOT(ISBLANK([End Date]#))), IF(AND([Start Date]# <= TODAY(), [End Date]# >= TODAY()), "In Progress", IF(AND([Start Date]# <= TODAY(), [End Date]# < TODAY()), "Completed", "Planned Not Started")), "Unplanned"))

Jeffrey Wagemans

Hi Emilio!

Thank you so much, it worked!