Formula for drop down lights with single-select drop down and date

aaddison

Hello Smartsheet Community,

I am attempting to make a project health show either green, yellow, red, blue or blank based on the following criteria, some strings seem to work but I seem to have the biggest problem with the selections of "0%", "100%" and making it blank if one of the sources is blank:

Green:

1. If Project percent complete is 100 % - can't get that to work, tried IF([Project percent Complete]@row <> "100%", "Green", and IF([Project percent Complete]@row = "100%", "Green",
2. If Project percent complete is 0 and proposed end date is 30+ days away - not working
3. If Project percent complete is any selection 1-99% (1-25%, 26-50%, 51-79%, or 80-99%) and proposed end date is 11+ days away - working

Yellow:

1. If Project percent complete is 0% and proposed end date is in the next 11-29 days - not working
2. If Project percent complete is any selection 1-99% (1-25%, 26-50%, 51-79%, or 80-99%) and Proposed end date is 3-10 days away - working

Red:

1. Project percent complete is 0% and proposed end date is in the next 10 days or the past - not working
2. If Project percent complete is any selection 1-99% (1-25%, 26-50%, 51-79%, or 80-99%) and Proposed end date is in the next 2 days or past - working

Blue:

1. If status is approved and stage of project is closed - working

Blank:

1. If Project percent complete is blank OR proposed end date is blank - this seems to work only for proposed end date and if project percent complete is 0%??? Tried IF(OR(ISBLANK([Project percent Complete]@row), ISBLANK([Proposed End Date]@row)), "",
2. If status is not approved and stage of project is closed (other status fields include Submitted, Denied, Postponed Temporarily, Postponed Indefinitely) - working

Here's the mass that seems to be working for 0%-99%, however, if proposed end date is blank, it shows red when 1-25%, 26-50%, 51-79%, or 80-99% are selected.

=IF(AND(Status@row = "Approved", [Stage of Project]@row = "Closed"), "Blue", IF(AND(Status@row <> "Approved", [Stage of Project]@row = "Closed"), "", IF(AND([Project percent Complete]@row = "1-25%", [Proposed End Date]@row - TODAY() > 10), "Green", IF(AND([Project percent Complete]@row = "1-25%", [Proposed End Date]@row < TODAY(11), [Proposed End Date]@row >= TODAY(3)), "Yellow", IF(AND([Project percent Complete]@row = "1-25%", [Proposed End Date]@row - TODAY() < 3), "Red", IF(AND([Project percent Complete]@row = "26-50%", [Proposed End Date]@row - TODAY() > 10), "Green", IF(AND([Project percent Complete]@row = "26-50%", [Proposed End Date]@row < TODAY(11), [Proposed End Date]@row >= TODAY(3)), "Yellow", IF(AND([Project percent Complete]@row = "26-50%", [Proposed End Date]@row - TODAY() < 3), "Red", IF(AND([Project percent Complete]@row = "51-79%", [Proposed End Date]@row - TODAY() > 10), "Green", IF(AND([Project percent Complete]@row = "51-79%", [Proposed End Date]@row < TODAY(11), [Proposed End Date]@row >= TODAY(3)), "Yellow", IF(AND([Project percent Complete]@row = "51-79%", [Proposed End Date]@row - TODAY() < 3), "Red", IF(AND([Project percent Complete]@row = "80-99%", [Proposed End Date]@row - TODAY() > 10), "Green", IF(AND([Project percent Complete]@row = "80-99%", [Proposed End Date]@row < TODAY(11), [Proposed End Date]@row >= TODAY(3)), "Yellow", IF(AND([Project percent Complete]@row = "80-99%", [Proposed End Date]@row - TODAY() < 3), "Red", IF(AND([Project percent Complete]@row = "0%", [Proposed End Date]@row - TODAY() > 29), "Green", IF(AND([Project percent Complete]@row = "0%", [Proposed End Date]@row - TODAY() < 11), "Red", IF(AND([Project percent Complete]@row = "0%", [Proposed End Date]@row > TODAY() + 10, [Proposed End Date]@row < TODAY() + 30), "Yellow", IF(OR(ISBLANK([Project percent Complete]@row), ISBLANK([Proposed End Date]@row)), ""))))))))))))))))))

Appreciate any help out there!

Paul Newcome

Here is my suggestion:

=IF(OR([Project percent Complete]@row = "", [Proposed End Date]@row = "", AND(Status@row <> "Approved", [Stage of Project]@row = "Closed")), "", IF(AND(Status@row = "Approved", [Stage of Project]@row = "Closed"), "Blue", IF(OR(AND([Project percent Complete]@row = 0, [Proposed End Date]@row<= TODAY(10)), AND([Project percent Complete]@row <> 0, [Project percent Complete]@row <> 1, [Proposed End Date]@row<= TODAY(2))), "Red", IF(OR([Project percent Complete]@row = 1, AND([Project percent Complete]@row = 0, [Proposed End Date]@row>= TODAY(30)), AND([Project percent Complete]@row <> 0, [Project percent Complete]@row <> 1, [Proposed End Date]@row>= TODAY(11))), "Green", "Yellow"))))

Paul McGuinness

Hi @aaddison

This formula appears to be working to rag based on the criteria outlined above -

=IF(OR([Project percent complete]@row = 1, AND([Project percent complete]@row = 0, [Proposed end date]@row - TODAY() >= 30), AND(OR([Project percent complete]@row > 0, [Project percent complete]@row < 1), [Proposed end date]@row - TODAY() >= 11)), "Green", IF(OR(AND([Project percent complete]@row = 0, [Proposed end date]@row - TODAY() >= 11), AND(OR([Project percent complete]@row > 0, [Project percent complete]@row < 1), [Proposed end date]@row - TODAY() >= 3)), "Yellow", IF(OR(AND(OR([Project percent complete]@row > 0, [Project percent complete]@row < 1), OR([Proposed end date]@row < TODAY(), [Proposed end date]@row - TODAY() <= 3)), AND([Project percent complete]@row = 0, OR([Proposed end date]@row - TODAY() <= 10, [Proposed end date]@row < TODAY()))), "Red", "")))

Here's my tester

You will need to add in the additional bits for Blue and Blank but without column names I couldn't create those.

I would suggest adding blue in front of my formula above, and adding the Blank part at the very end.

You may need to tweak the above formula but hopefully its got you closer to the solution.

Hope that helps

Thanks

Paul

Paul Newcome

Is you percent complete a dropdown type column with those exact options listed?

aaddison

Power to the Pauls! Thank you both for responding. 

Yes, my project percent complete is a single select drop down with those exact options (0%, 1-25%, 26-50%, 51-79%, 80-99%, 100%)

Here is the formula I have in here now (tacked on blue and blank bits to Paul McGuiness')

=IF(AND(Status@row = "Approved", [Stage of Project]@row = "Closed"), "Blue", IF(AND(Status@row <> "Approved", [Stage of Project]@row = "Closed"), "", IF(OR([Project percent Complete]@row = 1, AND([Project percent Complete]@row = 0, [Proposed End Date]@row - TODAY() >= 30), AND(OR([Project percent Complete]@row > 0, [Project percent Complete]@row < 1), [Proposed End Date]@row - TODAY() >= 11)), "Green", IF(OR(AND([Project percent Complete]@row = 0, [Proposed End Date]@row - TODAY() >= 11), AND(OR([Project percent Complete]@row > 0, [Project percent Complete]@row < 1), [Proposed End Date]@row - TODAY() >= 3)), "Yellow", IF(OR(AND(OR([Project percent Complete]@row > 0, [Project percent Complete]@row < 1), OR([Proposed End Date]@row < TODAY(), [Proposed End Date]@row - TODAY() <= 3)), AND([Project percent Complete]@row = 0, OR([Proposed End Date]@row - TODAY() <= 10, [Proposed End Date]@row < TODAY()))), "Red", "", IF(OR(ISBLANK([Project percent Complete]@row), ISBLANK([Proposed End Date]@row)), ""))))))

Green:

1. If Project percent complete is 100 % - works!!
2. If Project percent complete is 0 and proposed end date is 30+ days away - works!!
3. If Project percent complete is any selection 1-99% (1-25%, 26-50%, 51-79%, or 80-99%) and proposed end date is 11+ days away - still working!

Yellow:

1. If Project percent complete is 0% and proposed end date is in the next 11-29 days - not working, shows green, shows yellow 3-10 days
2. If Project percent complete is any selection 1-99% (1-25%, 26-50%, 51-79%, or 80-99%) and Proposed end date is 3-10 days away - still working!

Red:

1. Project percent complete is 0% and proposed end date is in the next 10 days or the past - not working, shows "incorrect argument set" for today-day 2 and then yellow through day 10
2. If Project percent complete is any selection 1-99% (1-25%, 26-50%, 51-79%, or 80-99%) and Proposed end date is in the next 2 days or past - not working, says “Incorrect argument set”

Blue:

1. If status is approved and stage of project is closed - still working!

Blank:

1. If Project percent complete is blank OR proposed end date is blank - not working, says “incorrect argument set” I could wrap the whole thing in IFERROR to show blank for these cases? Assuming we are able to fix the other “errors”?
2. If status is not approved and stage of project is closed (other status fields include Submitted, Denied, Postponed Temporarily, Postponed Indefinitely) - still working!

Thanks for reading, considering, testing, and assisting!

Paul Newcome

Here is my suggestion:

=IF(OR([Project percent Complete]@row = "", [Proposed End Date]@row = "", AND(Status@row <> "Approved", [Stage of Project]@row = "Closed")), "", IF(AND(Status@row = "Approved", [Stage of Project]@row = "Closed"), "Blue", IF(OR(AND([Project percent Complete]@row = 0, [Proposed End Date]@row<= TODAY(10)), AND([Project percent Complete]@row <> 0, [Project percent Complete]@row <> 1, [Proposed End Date]@row<= TODAY(2))), "Red", IF(OR([Project percent Complete]@row = 1, AND([Project percent Complete]@row = 0, [Proposed End Date]@row>= TODAY(30)), AND([Project percent Complete]@row <> 0, [Project percent Complete]@row <> 1, [Proposed End Date]@row>= TODAY(11))), "Green", "Yellow"))))

aaddison

Thank you very much, everything works as it should now!! And I have learned a lot from your syntax - not all criteria need to be spelled out the way I had been trying to do before, and I can think about formulas in a different way and have them shorter.

Paul Newcome

Happy to help. 👍️