IF/AND - Red, Yellow Green Formula
Hello! I'm trying to reflect the contract status based on a contract expiration date. Example - If contract does not expire for 45+ days, status is green. If the contract expires within 15-45 days, the status is yellow. If the contract expires within 15 days, the status is red. Unfortunately, the status shows as yellow if the contract expiration cell is blank and I've searched for solutions and I'm unable to find the answer. Below is the formula I'm using:
=IF([Contract Expiration Date]@row > TODAY() + 45, "Green", IF([Contract Expiration Date]@row < TODAY() + 45, "Yellow", IF([Contract Expiration Date]@row < TODAY() + 15, "Red", 0)))
I appreciate any help!
Best Answer
-
You could add the ISBLANK and tell it what you want to do.
=IF(ISBLANK([Contract Expiration Date]@row), "", IF([Contract Expiration Date]@row > TODAY() + 45, "Green", IF([Contract Expiration Date]@row < TODAY() + 45, "Yellow", IF([Contract Expiration Date]@row < TODAY() + 15, "Red", 0
IF(ISBLANK([Contract Expiration Date]@row, "Leave this blank if you want nothing or give it a status"
Answers
-
You could add the ISBLANK and tell it what you want to do.
=IF(ISBLANK([Contract Expiration Date]@row), "", IF([Contract Expiration Date]@row > TODAY() + 45, "Green", IF([Contract Expiration Date]@row < TODAY() + 45, "Yellow", IF([Contract Expiration Date]@row < TODAY() + 15, "Red", 0
IF(ISBLANK([Contract Expiration Date]@row, "Leave this blank if you want nothing or give it a status"
-
That works, thanks so much!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 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