IF AND NOT

Need help with some formulas

Important Columns : Schedule Health, Status, Target Start Date, Target End Date

Schedule Health will hold the formula - changing to RED GREEN and YELLOW Depending on formula

Green : IF Today is < than Target Start Date, AND/OR Status = "Complete"

Yellow = IF Today is >= Target Start Date, AND Today <= Target End Date AND Status is NOT "Complete" (Not sure if the Green status of "Complete" would override the need for the last AND/NOT)

RED = IF Today is < Target End date AND Status is not = "Complete"

I've tried a multitude of things and just cant seem to get it, please any help would be great!

Heres what I tried that I thought would work:

=IF(TODAY()>[Target Start Date]@row, AND(TODAY()<[Target End Date]@row, AND(NOT([Status]@row = "Complete"), "Yellow”, IF(TODAY()<[Target Start Date]@row OR([Status]@row = "Complete"), "Green", IF(TODAY()>[Target End Date]@row, “RED"

Find more posts tagged with

Formulas

Accepted answers

All comments

Samuel Mueller

@carly try this formula:

=IF(OR(TODAY()<[Target Start Date]@row, [Status]@row = "Complete"), "Green", IF(and(TODAY()>=[Target Start Date]@row, TODAY()<=[Target End Date]@row), "Yellow", “Red"))

Also please review this help article on the today formula, it could impact you and you may want to rework your formula.

Automatically update the TODAY function in formulas | Smartsheet Learning Center

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.