Status Change

Lee Wood

Good morning community

I hope you can help, I just can't seem to get this formula right...

I have the following status categories on a color wheel

Red - Overdue

Yellow - Task Outstanding

Blue - Investigation Outstanding

Green - Resolved

The conditions are as follows:

If Scheduled Date column is blank then the Status column is blank

If the Date Completed column is not blank then the Status column is Blue

If the Scheduled Date column has gone past today then the Status column is Red

If the Scheduled Date column is Today or in the future then the Status column is Yellow

If the Date Completed column is not blank then the Status column is Green

I think I have this in the correct order for the Status column to pick up priorities.

Thanks for your help in advance.

Lee

Paul Newcome

You have the same argument for two different colors. Are you able to clarify?

If Scheduled Date column is blank then the Status column is blank

If the Date Completed column is not blank then the Status column is Blue

If the Scheduled Date column has gone past today then the Status column is Red

If the Scheduled Date column is Today or in the future then the Status column is Yellow

If the Date Completed column is not blank then the Status column is Green

Lee Wood

Hi

My sincerest apologies, below is correct:

If Scheduled Date column is blank then the Status column is blank

If the Scheduled Date column has gone past today then the Status column is Red

If the Scheduled Date column is Today or in the future then the Status column is Yellow

If the Date Completed column is blank then the Status column is Blue

If the Date Completed column is not blank then the Status column is Green

Paul Newcome

Ok. So now I am unsure of exactly what you are looking for with the Blue. If the Date Completed is blank, then we would be looking a the Scheduled Date.

Lee Wood

Hi Paul,

Thank you for getting back to me.

Once again, my sincerest apologies that I haven't communicated this effectively enough.

The way I would like this to work (I'm open to suggestions on easier/better solutions) is essentially that there are 3 steps within the workflow on my sheet.

Step 1 is for somebody to raise a ticket via a form for a task to be actioned. When the task is due today (referenced by the Scheduled Date column) then the Status column would be yellow.

Step 2 is for the process owner to complete the task. Once they have completed the task they should tick the checkbox in the Task Actioned column, at which point the Status column would turn blue.

Step 3 is for the process owner to complete an investigation into the reason that the task was initially raised. Once the investigation is closed out then a date is added to the Date Completed column. Once they have input the date then the Status column should turn green.

The only other condition is that I would like to add is for the Status column to turn red if the Scheduled Date column is overdue (past today) and the Task Actioned column has not been ticked.

Paul Newcome

Ok. I think I understand now. Try this:

=IF([Date Completed]@row <> "", "Green", IF([Task Actioned]@row = 1, "Blue", IF([Scheduled Date]@row< TODAY(), "Red", IF([Scheduled Date]@row = TODAY(), "Yellow"))))