# Formula Not Operating Properly

Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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")))
```
• Options

Thank you! This has been driving me crazy!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!