Need formula for health ball with status and end date

Staci B

Hello--

Looking for a health formula as follows. Please note that statuses are linked from multiple sheets and the wordings are different on a few rows i.e. complete/ completed

Status is complete/ completed= green

Status is in process & in progress ) and the end date is less than 10 days out= yellow

Status is in process/ in progress or not started and end date is less than 10 days out= red

Status is blank with or without end date= blue

TIA

Eric Law

https://community.smartsheet.com/discussion/114657/need-formula-for-health-ball-with-status-and-end-date

I am confused. So you just need a "general" formula for this? Are the column names status and end date? Are these just for the individual sheets are are you rolling this into one sheet? Why aren't the verbiage the same throughout? Can you change that as it will make everything so much easier.

I am going to assume you want a general one and assume that the column names are status and end date. You can edit as you wish.

=IF(OR(ISBLANK(Status@row), ISBLANK([End Date]@row), "Blue", IF(Status@row="Complete", "Green", IF([End Date]@row<Today(10), "Red", "Yellow")))

The only thing you NEED to change if you don't update all sheets to be the same is the bolded Complete as we do not reference process or in progress.

Staci B

So the sheets that are linking into this sheet that I am creating are not "owned by me" and as of right now I dont have editor status to change the verbiage. I am making a master sheet that includes 5-6 other sheets. The columns are status and end date you are correct.

So I guess I would need to have both "complete" and "completed" mentioned in the formula to work properly.

Does in progress/process not need to be mentioned in the formula to meet the criteria I originally mentioned?

Thanks for your help

Paul Newcome

You have some logic missing.

Status is complete/ completed= green <-- Clear enough

Status is in process & in progress ) and the end date is less than 10 days out= yellow <-- What about when it is more than 10 days out?

Status is in process/ in progress or not started and end date is less than 10 days out= red <-- WHat if it is more than 10 days out? Also same as logic above except it also includes the "Not Started" status.

Status is blank with or without end date= blue <-- Clear enough