RYG Status w/ Date and Checkbox
I am trying to get my RYG Status column to auto update based on a couple of factors - I can seem to get them all to align.
If RFP Due Date is Blank = Gray
If RFP Due Date is in the future = Green
If RFP Due Date is within 3 days of today = Yellow (this one is not working)
If RFP Due Date is today or in the past and the RFP Submitted is not checked = Red (this one is not working)
If RFP Submitted is checked = Green
Here is my current formula:
=IF(ISBLANK([RFP Due Date]@row), "Gray", IF([RFP Submitted]@row = 1, "Green", IF([RFP Due Date]@row <= TODAY(3), "Yellow", IF([RFP Due Date]@row <= TODAY(), "Red", IF([RFP Due Date]@row > TODAY(), "Green")))))
Best Answer
-
Try this one: =IF(NOT(ISBLANK([Submitted Date]@row)), "Green", IF(ISBLANK([Due Date]@row), "Gray", IF([Due Date]@row <= TODAY(), "Red", IF(AND([Due Date]@row > TODAY(), [Due Date]@row <= TODAY(3)), "Yellow", IF([Due Date]@row > TODAY(), "Green")))))
-Dave
Answers
-
Hi @Adrienne Strong ,
In the section where you are checking if the date is within 3 days, it is also matching for dates that anytime in the past, that is way it appears that the "red" condition is not working. Adding an "And" should fix it.
Try this: =IF(ISBLANK([RFP Due Date]@row), "Gray", IF([RFP Submitted]@row = 1, "Green", IF(AND([RFP Due Date]@row >= TODAY(), [RFP Due Date]@row <= TODAY(3)), "Yellow", IF([RFP Due Date]@row <= TODAY(), "Red", IF([RFP Due Date]@row > TODAY(), "Green")))))
Thanks,
Dave
-
We've changed up the structure of how we want this sheet to function. We've removed the Submitted checkbox column and replaced it with a Submitted Date column.
If Due Date is Blank = Gray
If Due Date is in the future = Green
If Due Date is within 3 days of today = Yellow
If Due Date is today or in the past = Red
If Submitted Column has a date entered = Green (this one is not working)
=IF(ISBLANK([Due Date]@row), "Gray", IF(AND([Due Date]@row >= TODAY(), [Due Date]@row <= TODAY(3)), "Yellow", IF([Due Date]@row <= TODAY(), "Red", IF(AND([Due Date]@row > TODAY(), [Submitted Date]@row <= TODAY(), "Green")))))
I'm not sure if one of the other formulas is overriding the Submitted Date? I've tried this in different orders.
Thanks,
Adrienne
-
Try this one: =IF(NOT(ISBLANK([Submitted Date]@row)), "Green", IF(ISBLANK([Due Date]@row), "Gray", IF([Due Date]@row <= TODAY(), "Red", IF(AND([Due Date]@row > TODAY(), [Due Date]@row <= TODAY(3)), "Yellow", IF([Due Date]@row > TODAY(), "Green")))))
-Dave
-
Thank you!
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!