RAG Looking at Two Dates
SS Community,
I am hoping you can help with a formula problem I am trying to solve. I have a RAG column that is currently looking at a due date and based on some criteria it will return a RAG value. What I am trying to add to the formula when there is a revised due date added to look at that column instead and return the RAG based on the revised due date. Not all lines have a revised. due date
Here is my original formula:
=iF(Status@row = "Closed", "", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(8), "Green"))))
The new column added is called 'Revised Due Date'
Is this formula possible?
Thank you in advance for any guidance.
All the best,
Carl Vieira
Best Answer
-
You can try something on these lines -
=iF(Status@row = "Closed", "", IF([Revised Due Date]@row<>"", IF([Revised Due Date]@row < TODAY(), "Red", IF([Revised Due Date]@row <= TODAY(7), "Yellow", IF([Revised Due Date]@row >= TODAY(8), "Green"))), IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(8), "Green"))))
Essentially, if the 'Revised Due Date' is not blank, check it for the RAG criteria, else check the Due date for the RAG criteria.
Answers
-
You can try something on these lines -
=iF(Status@row = "Closed", "", IF([Revised Due Date]@row<>"", IF([Revised Due Date]@row < TODAY(), "Red", IF([Revised Due Date]@row <= TODAY(7), "Yellow", IF([Revised Due Date]@row >= TODAY(8), "Green"))), IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row <= TODAY(7), "Yellow", IF([Due Date]@row >= TODAY(8), "Green"))))
Essentially, if the 'Revised Due Date' is not blank, check it for the RAG criteria, else check the Due date for the RAG criteria.
-
This works great, I really appreciate your help. Looking at the formula that makes total sense, I was trying an IF/OR and was getting nowhere
Thank you,
Carl
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!