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
-
=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
-
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!
-
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.
-
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.
-
=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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!