Formula Help.

styless

I have a formula going on for the RYG. I added a new column, "Status". I wanted it to be If the status column is FILLED or CANCELLED, the ball will turned Green and canceled out all the other formatting. How can I corporate that into the existing formula below?

=IF(TODAY() - [Date Opened]@row <= 60, "Green", IF(AND(TODAY() - [Date Opened]@row <= 124, TODAY() - [Date Opened]@row >= 61), "Yellow", "Red"))

Sample Sheet: https://app.smartsheet.com/b/publish?EQBCT=e809c0bc9ef046fb8742837615bf7e35

 

Thanks for the help!

Mike Wilday

I updated the formula in the sheet. 

=IF(OR(Status@row = "Cancelled", Status@row = "Filled"), "Green", IF(TODAY() - [Date Opened]@row <= 60, "Green", IF(AND(TODAY() - [Date Opened]@row <= 124, TODAY() - [Date Opened]@row >= 61), "Yellow", "Red")))

This will default your health to green if the status is filled or Cancelled. 

styless

Hi Mike, Thank you so much. It worked well! Just a quick follow up question, how do I incorporate the IF the "Date Opened" or the "Status" is blank, it will just show N/A? Right now is showing all Green even on the row with no data on it.

Thanks again!

Mike Wilday

Here you go:

=IF(OR(ISBLANK(Status@row), ISBLANK([Date Opened]@row)), "N/A", IF(OR(Status@row = "Cancelled", Status@row = "Filled"), "Green", IF(TODAY() - [Date Opened]@row <= 60, "Green", IF(AND(TODAY() - [Date Opened]@row <= 124, TODAY() - [Date Opened]@row >= 61), "Yellow", "Red"))))

I added it to the sheet as well. 

styless

Thanks man. You just made my day!

Mike Wilday

Haha. I'm glad I could help you out.