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.

Ideas?

Find more posts tagged with

Accepted answers

All comments

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.

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.