Changing a symbol based on the data in 2 columns

David White

Hi,

I am trying to have a symbol column change based on the data in 2 columns.

Column a is The proposed finish date of the task - Called Finish and Column b is the % completed of the task - Called % Complete.

I am trying the following with no luck, but as i am relatively new, this is not surprising TBH.

=IF(Finish@row, <Today, AND(% Complete@row, <100, = "Red"))

I would change the % Complete and <"Days" to show alternate colours.

Any advice assistance would be much appreciated.

Thanks in advance!

David

Kelly Moore

Hey @David White

=IF(AND(Finish@row<Today(), [% Complete]@row<1), "Red")

If you have difficulty adding the remaining criteria for the other colors, let me know.

Kelly

David White

https://community.smartsheet.com/discussion/comment/365089#Comment_365089

Thank you @Kelly Moore that is great.

I assume, most likely incorrectly that i would continue like this:

=IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), "Red"), IF(AND(Finish@row < Today(), [% Complete]@row >.90), "Green"), IF(AND(Finish@row < Today(), [% Complete]@row < .5), "Yellow"), IF(AND(Finish@row < Today(), [% Complete]@row <.2), "Grey")

?

Kelly Moore

Hey @David White

I made a few adjustments to your syntax

=IF(AND(Finish@row < TODAY(), [% Complete]@row < 1), "Red", IF(AND(Finish@row < Today(), [% Complete]@row >.90), "Green", IF(AND(Finish@row < Today(), [% Complete]@row < .5), "Yellow", IF(AND(Finish@row < Today(), [% Complete]@row <.2), "Grey"))))

The syntax is now correct in the formula but the logic isn't. You're going to either get blanks or Red. Did you mean for all of your date criteria to be less than TODAY()? What happens if the date is the current date? Or a date in the future? And typically, regardless of the Finish date, once a % completion = 100% then the status goes to green.

If you write out in words your criteria I will help you with the formula.

Let me know

Kelly

David White

@Kelly Moore

Thank you so much!

What i am looking for is:

Red if Finish date is passed, and % Complete is less than 100%

Yellow if Today is within a week/5 Days of Finish Date and/Or % Complete is less than 50%

Green If Today is Less than Finish Date and/or % complete is 90-100% (100% will change entire line to complete, in separate formula, which is written)

Grey if Today is within 1 week of Start Date and/or % Complete is 0%

I hope that make sense, and is straight forward?

THank you so much

Kelly Moore

Hey @David White

Here's what is written so far but there are still gaps that aren't covered.

=IF(AND(ISDATE(Finish@row), ISDATE(Start@row)), IF(AND(Finish@row <= TODAY(), [% Complete]@row < 1), "Red", IF(AND(TODAY() >= Start@row, TODAY() <= Start@row + 7, [% Complete]@row = 0), "Gray", IF(AND(Finish@row >= TODAY(), Finish@row <= TODAY(5), [% Complete]@row <= 0.5), "Yellow", IF(AND(Finish@row > TODAY(), [% Complete]@row > 0.9), "Green")))))

You had said And/Or in your criteria. I wrote them as AND's. An AND vs OR are very different so let me know exactly which is correct. An AND says both criteria must be present. An OR says either condition will satisfy the True condition.

For the Gray, when you said within one week of start - did you mean that Today was one week or less past the start date, or that you were one week approaching the start date. I assumed the first one since I wouldn't expect the %Complete to have any value until after the start. Is that right?

In your conditions, you have nothing that says what to do if Finish date is still in future but %Complete is greater than 50%.

Please insert formula above and play with different dates in your Start, Finish and also different percentages. See what is giving you expected results and what is not.

Kelly

David White

@Kelly Moore

Thank you for this. I have entered it into our sheet and noted that the grey's arent working so i assume we may have some conflicts in the data and the formula. (more likely the data entered).

I am keen on the AND OR, as if the %Complete is less than 50%, it is still behind to a certain extent. So AND OR would keep this yellow, and some of the pressure on the team to keep it moving.

The Grey we would like to show as upcoming, so essentially within a week of the start date and therefore gives the team the heads up on what is happening in the near future. I have the project coordinator playing with the dates at the moment, so there is some changes happening, and would like his input on the current formula, given he is managing this sheet.

Apart from the AND OR, i think we are there, and we really do appreciate the assistance. It will certainly help me learn how to build these formulas in future.

Thank you

David

Kelly Moore

Hey @David White

I don't understand what you mean with AND OR in your post above. What criteria is an AND and what criteria is an OR? These are two functions, and yes they can be combined, but they are not automatically combined and they are definitely not the same thing.

For example

IF(OR([% Complete]@row=1, AND(Finish@row < Today(), [% Complete]@row >.90)), "Green")

This says if %Complete = 100%, OR IF Finish is overdue AND %Complete then your status is Green.

I'm going to make a guess at what may help

=IF(AND(TODAY() >= Start@row, TODAY() <= Start@row + 7, [% Complete]@row = 0), "Gray", IF(AND(ISDATE(Finish@row), ISDATE(Start@row)), IF(AND(Finish@row <= TODAY(), [% Complete]@row < 1), "Red", IF(AND(Finish@row >= TODAY(), Finish@row <= TODAY(5), [% Complete]@row <= 0.5), "Yellow", IF(AND(Finish@row > TODAY(), [% Complete]@row >= 0.9), "Green", IF(AND(Finish@row >= TODAY(), [% Complete]@row >= 0.5), "Yellow"))))))

Kelly

David White

Hi @Kelly Moore

Yes i think we are getting a little mixed up, sorry for my poor explanation.

Maybe if I go back to the start.

Green = 100% Complete from the % Complete column

Green also = if the Date is within the Start and Finish dates and is above 50% Complete

Yellow = Less than 50% complete but within Start and Finish Date

Red = less than 100% Complete and outside of Start and finish date

Gray = Prior to start date

I think this will be much easier and reduces cross over. Apologies for my poor description and many thanks for all your help!

Kelly Moore

Hey @David White

The logic works with your last post - no gaps in percentages or conflicting date ranges.

=IF(AND(ISDATE(Start@row), ISDATE(Finish@row)), IF(TODAY() < Start@row, "Gray", IF(OR([% Complete]@row = 1, AND(TODAY() >= Start@row, TODAY() < Finish@row, [% Complete]@row > 0.5)), "Green", IF(AND(TODAY() >= Finish@row, [% Complete]@row < 1), "Red", IF(AND(TODAY() >= Start@row, TODAY() < Finish@row, [% Complete]@row <= 0.5), "Yellow")))))

Does this give you all the expected results? Don't hesitate to let me know if it needs to be tweaked

Kelly

David White

@Kelly Moore

Apologies for the slow response, i have been up to my ears in the project.

Thank you for this, it works perfectly and as expected. I really appreciate the assistence on this.

Many thanks

David