I'm trying to create a nested IF formula and am stumped. Please help!

My columns are: Finish ... which is a date field, Status ... which can be Complete, Not Started or In Progress, and a column where I want to insert a Green, Yellow, or Red symbol based on logic.

My needs: If the finish date is in the past and the status is not 'complete' I want to insert a RED symbol. If the finish date with within 5 days of today's date and the status is not 'complete' I want to insert a Yellow symbol. Otherwise symbol should be inserted as Green.

My stab at a formula:

=IF(AND(TODAY() > [Finish]@row, [Status]@row <> "Complete", "Red"), IF[Finish]@row < TODAY(+5), "Yellow"), "Green")

Any advice?

Find more posts tagged with

Accepted answers

Katie G

As Julie said, yes, just a couple of parentheses -- so close! You may also want to add in the check if it's complete before coloring yellow?

=IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(AND(Finish@row < TODAY(+5), Status@row <> "Complete"), "Yellow", "Green"))

All comments

Julie Fortney

Hi @Parker103

Try this:

=IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(Finish@row < TODAY(+5), "Yellow", "Green"))

All I did was change around some parentheses. You were close!

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

Zachary Hall

Here's what I would recommend.

=IF([Status]@row = "Complete", "Green", IF(TODAY()>Finish]@row, "Red" , IF([Finish]@row<TODAY(5) , "Yellow" , "Green"))

Hope it helps!

Katie G

As Julie said, yes, just a couple of parentheses -- so close! You may also want to add in the check if it's complete before coloring yellow?

=IF(AND(TODAY() > Finish@row, Status@row <> "Complete"), "Red", IF(AND(Finish@row < TODAY(+5), Status@row <> "Complete"), "Yellow", "Green"))

Julie Fortney

Yes @Katie G , that is a great point!

Parker103

Thanks VERY much for the help. Formula is working great :)

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.