Status Change

Options

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

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Lee Wood ✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Lee Wood ✭✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!