Formula Not Operating Properly

Hello, I am testing out the Construction/Facilities Management Template Set and I think the formula is incorrect in the sheet for the Schedule Health.

=IF(State3 = "Complete", "Green", IF(AND(State3 <> "Complete", TODAY() > [End Date]3), "Red", IF(AND(State3 = "Not Started"), "Blue", "Yellow")))

The way it is currently operating is if the status changes to complete it turns green (good), however any other status; in progress, not started, the button turns red. Can someone advise on how the formula should read?

Ideally I think we would want red when due date is past due, blue when not started, yellow in progress.

Thank you

Find more posts tagged with

Accepted answers

Parker Oxford

You're going to have to maneuver the formulas such that red is the last option since the qualifications for it will be satisfied by things you want other colors to be used for.

I included an example of this below. If you have a status for "State" that contains "In Progress", you can use that to add to the formula to get a more exact end result.

=IF(State3 = "Complete", "Green", IF(AND(State3 = "Not Started"), "Blue", IF(AND(State3 <> "Complete", TODAY() > [End Date]3), "Red",  "Yellow")))

All comments

Parker Oxford

You're going to have to maneuver the formulas such that red is the last option since the qualifications for it will be satisfied by things you want other colors to be used for.

I included an example of this below. If you have a status for "State" that contains "In Progress", you can use that to add to the formula to get a more exact end result.

=IF(State3 = "Complete", "Green", IF(AND(State3 = "Not Started"), "Blue", IF(AND(State3 <> "Complete", TODAY() > [End Date]3), "Red",  "Yellow")))

DA_Associates_PM

Thank you! This has been driving me crazy!

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.