Formula Functionality: RYGB Status Change Based on Date

Hello!

I have reviewed Smartsheet Discussions quite a bit, but this is my first post. I am trying to correct a formula I made to change RYGB statuses. Currently the status correctly changes based on the following:

Blue if a Date Resolved is entered.
Green if the # of calendar days is > or = to 15 days.
Yellow if the # of calendar days is < or = to 14 days.

However, the status is not changing to red if the # of calendar days is < or = to 7 days. I am not sure what else I can change while using the ISDate and TODAY functions. Do I need to change these completely? The formula and a screenshot of the formula in the sheet is below!

=IF(ISDATE([Date Resolved]@row), "Blue", IF(AND([Foreclosure Sale Date]@row >= TODAY(15), ISBLANK([Date Resolved]@row)), "Green", IF(AND([Foreclosure Sale Date]@row <= TODAY(14), ISBLANK([Date Resolved]@row)), "Yellow", IF(AND([Foreclosure Sale Date]@row <= TODAY(7), ISBLANK([Date Resolved]@row)), "Red"))))

Find more posts tagged with

Sheets

Sharing and Collaboration

Formulas

functionality

Project Management

Accepted answers

SteyJ

The reason it's not changing to red is because yellow is True at the same time Red is true.

Try this formula:

=IF([Date Resolved]@row <> "", "Blue", IF([Foreclosure Sale Date]@row >= TODAY(15), "Green", IF(AND([Foreclosure Sale Date]@row < TODAY(15), [Foreclosure Sale Date]@row > TODAY(7)), "Yellow", "Red")))

All comments

SteyJ

The reason it's not changing to red is because yellow is True at the same time Red is true.

Try this formula:

cholt24

Thank you @SteyJ ! I should have caught that. I appreciate it!

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.