Help with If/Then statement evaluating condition of equipment life

scott.95

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?

John_Foster

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

scott.95

That worked! It's either awfully simple or simply awful! Thanks John!

scott.95

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.

Nick Korna

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!