Formula Functionality: RYGB Status Change Based on Date

cholt24

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"))))

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")))

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")))

cholt24

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