Formula to join three different options to provide a status

Cathy Fraser

Hi, I am looking to join three different columns to provide the current status (Red, Yellow, Green, Gray). I have tried a few different formulas but can not seem to join them together. Below are the separate formulas that work, but I not able to join the formula together at this stage.

=IF(NOT(ISBLANK([Cleaning booked in]@row)), "Yellow", "Red")

=IF(NOT(ISBLANK([Tool Box Time & Date]@row)), "Green")

=IF([COVID Restrictions Now Lifted]@row, "Gray")

So I am looking at the formula to look at three different locations to give me back The status, the status flow is

Newline created = Red

The date has been provided for the clean = Yellow (Cleaning booked in = Date field)

Toolbox talk has been carried out = Green (Tool Box Time & Date = text field)

COVID Restrictions have been lifted = Gray (COVID Restrictions now Lifted = tick box)

Please let me know if you need more information, thanks in advance.

Regards

Cathy

Jeff Reisman

@Cathy Fraser

I think you might be better off using automation rules with Change a Cell actions.

Rule 1:

Trigger: When a row is changed, when [Cleaning Booked in] changes to any value

Action: Change Status to "Yellow"

Rule 2:

Trigger: When a row changes, when Tool Box Time & Date changes to any value

Action: Change Status to "Green"

Rule 3:

Trigger: When a row changes, when COVID Restrictions Now Lifted changes to Checked

Action: Change Status to "Gray"