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

  • John_Foster
    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

  • 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.

  • Nick Korna
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!