RAG status formula using TODAY and end date

Hey smartsheet community, any help fixing this RAG status formula?


I am trying to automatically show RYGG balls dependant on the end date and today's date. With the exception of cancelled and complete tasks;

  • any task with the end date TODAY or in the future should be green
  • any task up to 3 working days in the past should be yellow
  • and any task over 3 working days in the past should be red


My current formula seems to work for green, yellow, gray and cancelled - but not for red. It also doesn't take into account working days.

=IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row - TODAY() < 0, "Yellow", IF([End Date]@row - TODAY(-3) < 0, "Red", "Green")))))


Any help would be amazing!!


Answers

  • L_123
    L_123 ✭✭✭✭✭✭

    =IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF(TODAY() >= [End Date]@row, "Green", IF([End Date]@row >=today()-3, "Yellow","Red"


    Give that a try

  • Anna Keeton
    Anna Keeton ✭✭✭

    Hi thanks for the help - though it doesn't seem to have quite worked yet.

    With a bit of twiddling I've got: =IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF(TODAY() = [End Date]@row, "Green", IF([End Date]@row = TODAY(-3), "Yellow", IF([End Date]@row <= TODAY(-4), "Red")))))


    But future tasks are not showing as green, and the formula still seems to use net days rather than working days - any suggestion on how to work around those 2 bits?


    Thanks a bunch!

  • L_123
    L_123 ✭✭✭✭✭✭

    My bad I got a sign mixed up.

    =IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF([End Date]@row >= TODAY(), "Green", IF([End Date]@row >= TODAY() - 3, "Yellow", "Red"))))


    Yours only has equal values, not ranges, so it wouldn't for example check if the end date was yesterday.

  • Anna Keeton
    Anna Keeton ✭✭✭

    Thanks @L@123 that seems to have worked better! thank you :)

    The only thing that's missing, is setting the formula to use working days. i.e at the moment 2 working days ago is showing as red, but it should be yellow

    Is there a way to make the calcualtion based on working days (Mon to Fri)?



  • Thank you, all, for being so helpful.

    I want to add something more to this formula:

    =IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", IF([End Date]@row >= TODAY(), "Green", IF([End Date]@row >= TODAY() - 3, "Yellow", "Red"))))

    I have 4 RAG statuses (green, yellow, red, blue). I want to have a "blue" status if there is no end date in the column. That will be a sign to me I need to define the end day.

    Thank you for your help.

  • L_123
    L_123 ✭✭✭✭✭✭

    =IF(Status@row = "Complete", "Gray", IF(Status@row = "Cancelled", "N/A", if(isblank([End Date]@row),"Blue", IF([End Date]@row >= TODAY(), "Green", IF([End Date]@row >= TODAY() - 3, "Yellow", "Red"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!