Formula for RYGB Status

tlong2b

Hi, I'm currently using this formula for status health but it's not the best because "Not Started" status is not showing Red if the End Date for the task is in the past. Obviously, the task is overdue even if it hasn't started. Can anyone assist to make this formula better?

=IF(Status@row = "Complete", "Blue", IF(Status@row = "Canceled", "", IF(Status@row = "Not Started", "", IF(ISBLANK([End Date]@row), "Blue", IF([End Date]@row >= TODAY(), "Green", IF([End Date]@row >= TODAY() - 3, "Yellow", "Red"))))))

Jeff Reisman

Here it is in the same order as the criteria grid:

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318226#Comment_318226

I just kept working the problem. Using helper columns to handle some of the functions outside of your main formula is key to making a lot of more complex things work in Smartsheet. Writing out a list of all possible combinations of your criteria really helps with getting a good idea of the right order for your nested IF statements. This trick of combining two disparate criteria (date and text) into one alphanumeric value is one I've used on quite a few Smartsheet projects.

Jeff Reisman

@tlong2b

The reason this is happening is because as the system is evaluating your IFs, when it finds the status = Not Started, it applies the positive condition for that nested IF --blank-- and stops evaluating. Since you're wanting to evaluate on two criteria columns (Status and End Date), using some ORs and ANDs might help, but you have to figure out which criteria are most important and put them first.

=IF(OR(Status@row = "Complete", ISBLANK([End Date]@row), "Blue", IF(Status@row = "Canceled", "", IF([End Date]@row >= TODAY(), "Green", IF(AND([End Date]@row >= TODAY(-3), Status@row <> "Complete"), "Yellow", IF(OR(Status@row = "Not Started", [End Date]@row < TODAY(-3), "Red")))))

If the Status is Complete or End Date is blank, make it blue;

otherwise, If the Status is Canceled, make it blank;

otherwise, if the End Date is today or after today, make it green;

otherwise, if the End Date is equal to or after three days ago (but not today or after today), and the status is NOT Complete, make it yellow;

otherwise, if the status is Not Started or the End Date is earlier than 3 days ago, make it red.

Note: Use the Smartsheet parentheses color coding to make sure I have the right number of them at the end there!

tlong2b

@Jeff Reisman Thank you Jeff for the explanation! I used your formula and I get a #Incorrect Argument Set. I'm thinking there's a missing ) or (. It looks like there are enough parentheses at the end though. I'm not proficient at this so your help is appreciated.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318163#Comment_318163

Looks like I'm missing one right near the end, after TODAY(-3)!

[End Date]@row < TODAY(-3)), "Red")))))

tlong2b

@Jeff Reisman I was so hoping this would be the resolution. It doesn't seem to turn red for those items which are "Not Started" and overdue. Also, I don't have anything for "In Progress" though that's my fault I didn't state I needed that in the beginning. Thanks again!

tlong2b

tlong2b

@Jeff Reisman I messed up in last response - actually the formula still shows #Incorrect Argument Set or #Unparseable, depending on how many parentheses at the end - I've tried adding and taking away. I don't know what's wrong.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318180#Comment_318180

You might have to just play around with the order or things in your logic.

Since you are using a lot of criteria, you could also create a "Helper" column to combine a text value representing the end date criteria with the status:

=IF([End Date]@row >= TODAY(), "ONT", IF([End Date]@row >= TODAY(-3), "DUE", IF(ISBLANK([End Date]@row), "NON", IF([End Date]@row < TODAY(-3), "LAT")))) + " " + Status@row

Then, based on your logic, you lay out a grid with what color each End Date/Status combo should be, allowing you to group results together in your big IF formula. (I added "Canceled" in here for anything with a status of Canceled, to differentiate those from rows with no end date.)

ONT Complete = Blue

ONT In Progress = Green

ONT Not Started = Green

ONT Canceled = Canceled

DUE Complete = Blue

DUE In Progress = Yellow

DUE Not Started = Yellow

DUE Canceled = Canceled

LAT Complete = Blue

LAT In Progress = Red

LAT Not Started = Red

LAT Canceled = Canceled

NON Complete = Blue

NON In Progress = ""

NON Not Started = ""

NON Canceled = Canceled

=IF(Status@row = "Complete", "Blue", IF(OR(Helper@row = "ONT In Progress", Helper@row = "ONT Not Started"), "Green", IF(OR(Helper@row = "DUE In Progress", Helper@row = "DUE Not Started"), "Yellow", IF(OR(Helper@row = "LAT In Progress", Helper@row = "LAT Not Started"), "Red", IF(OR(Helper@row = "NON In Progress", Helper@row = "NON Not Started"), "", IF(Status@row = "Canceled", "Canceled"))))))

I think the order makes sense here:

Anything with status of complete is blue,

On time AND in progress or on time AND not started is green,

Due in the past three days AND in progress or Due in the past three days AND not started is yellow,

Late AND in progress or Late AND not started is red,

No end date AND in progress or no end date AND not started is blank,

Anything with status of canceled is Canceled.

All this is tested and working:

Jeff Reisman

Here it is in the same order as the criteria grid:

tlong2b

Oh my gosh. I don't know what you did, but it works! Thank you, thank you! This is gold.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/318226#Comment_318226

I just kept working the problem. Using helper columns to handle some of the functions outside of your main formula is key to making a lot of more complex things work in Smartsheet. Writing out a list of all possible combinations of your criteria really helps with getting a good idea of the right order for your nested IF statements. This trick of combining two disparate criteria (date and text) into one alphanumeric value is one I've used on quite a few Smartsheet projects.