Updating the Red,Green,Yellow Status Column based on Dates from End date Column

I'm trying to create a formula that changes my status column to the red, yellow, red symbols.

I want to base it on my end date column.

I looked at some examples on the site, but i couldn't quite get it to work on my sheet. I was trying to set up a formula around the following parameters.

Red= Past End Date

Yellow= Within 90 Days of the End Date

Green= more than 90 days until End Date.

Also, there are some cells with N/A. will that affect some formulas?

Thanks for any help.

Find more posts tagged with

Accepted answers

Matt Johnson

Hi @RogerWyatt

I think this is the formula you are looking for.

=IFERROR(IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(90), "Yellow", "Green")), "")

There's probably some other variations of this that'll work too.

Quick note: If you are new to column formulas, this is a great use case for that. Right click on the column itself and choose "Convert to Column Formula".

I hope this helps.

Matt

All comments

Matt Johnson

Hi @RogerWyatt

I think this is the formula you are looking for.

=IFERROR(IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(90), "Yellow", "Green")), "")

There's probably some other variations of this that'll work too.

Quick note: If you are new to column formulas, this is a great use case for that. Right click on the column itself and choose "Convert to Column Formula".

I hope this helps.

Matt

RogerWyatt

Thank You.

Worked like a charm.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.