Status Symbols from Multiple Date Columns
I'm looking to have a health status (green, yellow, red) populate by checking multiple columns with differing expiration dates.
I'm trying to come up with a formula that will return the following status':
IF[Orientation Date] is in the past and [First Aid Ex Date], [OTS Exp Date], [BBP Exp Date] are at all least 30 days in the future, than "Green"
IF[Orientation Date] is in the past and any [First Aid Ex Date], [OTS Ex Date], [BBP Ex Date] are less than 90 days in future, than "Yellow"
IF any [First Aid Ex Date], [OTS Ex Date], [BBP Ex Date] are missing/blank or in the past, than "Red"
Here is a screenshot of the column names/order.
Any advice is appreciated!
Answers
-
Hi @dowelle For this, you'll want to use combinations of IF statements and AND and OR functions. The AND function means that all things must be true. The OR function means that any of the things can be true.
Give this one a try...
=IF(OR(ISBLANK([Orientation Date]@row), ISBLANK([OTS Ex Date]@row), ISBLANK([BBP Ex Date]@row), [OTS Ex Date]@row < TODAY(), [BBP Ex Date]@row < TODAY()), "Red", IF([Orientation Date]@row < TODAY(), IF(AND([First Aid Ex Date]@row < TODAY(30), [OTS Ex Date]@row < TODAY(30), [BBP Ex Date]@row < TODAY(30)), "Green", IF(OR([First Aid Ex Date]@row < TODAY(90), [OTS Ex Date]@row < TODAY(90), [BBP Ex Date]@row < TODAY(90)), "Yellow", "")), ""))
Please let me know if that works out for you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!