Help with If/Then statement evaluating condition of equipment life
Hi, I have a sheet with [Asset Status], [End of Life Date] and I'm trying to get a {red,green,yellow,gray} button returned. My formula below works as long as there is a date in [End of life date]. I'm out of ideas why.
=IF([Asset Status]@row <> "Active", "Gray", IF(AND([Asset Status]@row = "Active", [End of Life Date]@row = " "), "Gray", IF(AND([Asset Status]@row = "Active", [End of Life Date]@row > TODAY()), "Green", "Red")))
Ideas?
Answers
-
Hi @scott.95,
I would amend the formula to remove the space at the end of the top line between the quotes which should enable the formula to work as below.
=IF([Asset Status]@row <> "Active", "Gray", IF(AND([Asset Status]@row = "Active", [End of Life Date]@row = ""), "Gray", IF(AND([Asset Status]@row = "Active", [End of Life Date]@row > TODAY()), "Green", "Red")))
There is no condition in the formula to show Yellow, so I am not sure whether that is something you want to add as another option.
Hope this helps!
John
-
That worked! It's either awfully simple or simply awful! Thanks John!
-
I can determine the "yellow" now and will work on that part. I want yellow to be when the asset is within 6 months of its end of life date. Do you know how that would work? I'm guessing another AND statement with [asset status] [end of life date] greater than???? and less than [end of life date]. I'm open to suggestions.
-
Hi @scott.95,
You're correct - it would be something along the lines of:
=IF([Asset Status]@row <> "Active", "Gray", IF(AND([Asset Status]@row = "Active", [End of Life Date]@row = ""), "Gray", IF(AND([Asset Status]@row = "Active", [End of Life Date]@row <= TODAY(180), [End of Life Date]@row > TODAY()), "Yellow", IF(AND([Asset Status]@row = "Active", [End of Life Date]@row > TODAY()), "Green", "Red"))))
Sample output (largely to show the yellow symbol effect):
Hope this helps, but if I've misunderstood something or you have any problems/queries then just post!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 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
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!