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([email protected] = "Complete", "Gray", IF([email protected] = "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!!