# Looking for Formula Help - Health Status based on End Date versus Today

Options
✭✭✭✭

Good morning! I am looking for help with a formula as what I have entered returns "Unparseable".

I am looking to return colors based on the below:

If Status is Complete then "Green"

If Status is in Progress but less than TODAY "Red"

If Status is Not Started or In Progress and the End Date is greater than today by 7 days then "Yellow"

If Status is Not Started or In Progress and if End Date is greater than TODAY by 14 days then "Green"

If Status is NA then Blank

If Status is On Hold then Gray

I may just have too many arguments?

Below is how I entered it

=IF(Status@row = "Complete", "Green", IF(Status@row = "In Progress", [End Date]@row < TODAY(), "Red", IF(Status@row = "Not Started", [End Date]@row > TODAY(7), "Yellow", IF(Status@row = "In Progress", IF([End Date]@row > TODAY(14), "Green", IF(Status@row = "On Hold", "Gray", IF(Status@row = "NA", "", IF(Status@row = "In Progress", IF([End Date]@row > TODAY(7), "Yellow", IF(Status@row = "Not Started", IF([End Date]@row > TODAY(14), "Green"))))))))))))

Tags:

• ✭✭✭✭✭✭
Options

That would still leave a gap for 7, 8, or 9 days. You can use whatever works best for you. You could use greater than 7 to pick up right off of the yellow, you can adjust the yellow to be less than or equal to 14 days, you can leave it blank, or you can adjust both the yellow and green number of days to meet somewhere in the middle.

• ✭✭✭✭✭✭
Options

It looks like your syntax is a bit off. Try this:

=IF(Status@row <> "N/A", IF(Status@row = "On Hold", "Gray", IF(OR(Status@row = "Complete", [End Date]@row> TODAY(14)), "Green", IF([End Date]@row< TODAY(), "Red", IF([End Date]@row<= TODAY(7), "Yellow")))))

One thing I notice you do not account for is if the end date is between 7 and 14 days in the future. If less than 7 then yellow. If more than 14 then green. But what about the week in between?

• ✭✭✭✭✭✭
Options

Hello @MRosko02,

Your IF statements are kind of off, you should max have 3 fields, but your second inbedded if has 4 which makes that unparseable as it is not a valid formula. You should create individual formulas first to see if they work and then start nesting them.

• ✭✭✭✭
Options

@Paul Newcome Paul, that makes sense. So I should have put greater than 9 days?

• ✭✭✭✭✭✭
Options

That would still leave a gap for 7, 8, or 9 days. You can use whatever works best for you. You could use greater than 7 to pick up right off of the yellow, you can adjust the yellow to be less than or equal to 14 days, you can leave it blank, or you can adjust both the yellow and green number of days to meet somewhere in the middle.

• ✭✭✭✭
Options

@Paul Newcome Thank you so much! That works great!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!