Formula Functionality: RYGB Status Change Based on Date
Hello!
I have reviewed Smartsheet Discussions quite a bit, but this is my first post. I am trying to correct a formula I made to change RYGB statuses. Currently the status correctly changes based on the following:
- Blue if a Date Resolved is entered.
- Green if the # of calendar days is > or = to 15 days.
- Yellow if the # of calendar days is < or = to 14 days.
However, the status is not changing to red if the # of calendar days is < or = to 7 days. I am not sure what else I can change while using the ISDate and TODAY functions. Do I need to change these completely? The formula and a screenshot of the formula in the sheet is below!
=IF(ISDATE([Date Resolved]@row), "Blue", IF(AND([Foreclosure Sale Date]@row >= TODAY(15), ISBLANK([Date Resolved]@row)), "Green", IF(AND([Foreclosure Sale Date]@row <= TODAY(14), ISBLANK([Date Resolved]@row)), "Yellow", IF(AND([Foreclosure Sale Date]@row <= TODAY(7), ISBLANK([Date Resolved]@row)), "Red"))))
Best Answer
-
The reason it's not changing to red is because yellow is True at the same time Red is true.
Try this formula:=IF([Date Resolved]@row <> "", "Blue", IF([Foreclosure Sale Date]@row >= TODAY(15), "Green", IF(AND([Foreclosure Sale Date]@row < TODAY(15), [Foreclosure Sale Date]@row > TODAY(7)), "Yellow", "Red")))
Sincerely,
Jacob Stey
Answers
-
The reason it's not changing to red is because yellow is True at the same time Red is true.
Try this formula:=IF([Date Resolved]@row <> "", "Blue", IF([Foreclosure Sale Date]@row >= TODAY(15), "Green", IF(AND([Foreclosure Sale Date]@row < TODAY(15), [Foreclosure Sale Date]@row > TODAY(7)), "Yellow", "Red")))
Sincerely,
Jacob Stey
-
Thank you @SteyJ ! I should have caught that. I appreciate it!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!