Working on creating an IF statement that has multiple conditions to be met.

Ian Abee

I am working on trying to control the Harvey Ball colors based on status AND dates. The conditions I am trying to use are:

• If status is "complete”, then blue
• If start date is in the past but status is “not started”, then Harvey Ball, yellow
• If finish date is in the past and status is not “complete”, then Harvey Ball, red

I have a Start Date and Finish Date column, as well as a Progress Column with a dropdown select of Not Started, In Progress, and Completed. Then I have a status column where I want to put pretty much a column formula that will populate the row with the correct color harvey ball based on the conditions above. I was able to easily due the first condition although am struggling creating a formula for the last two and then eventually nest all three together. Thank you so much for your help if any one is able!

John Jonassen

Hi @Ian Abee ,

Give this formula a try:

=IF(Progress@row = "Complete", "Blue", IF(AND([Start Date]@row <= TODAY(), Progress@row = "Not Started"), "Yellow", IF(AND([Finish Date]@row <= TODAY(), Progress@row <> "Complete"), "Red", "Green")))

John Jonassen

Hi @Ian Abee ,

Give this formula a try:

=IF(Progress@row = "Complete", "Blue", IF(AND([Start Date]@row <= TODAY(), Progress@row = "Not Started"), "Yellow", IF(AND([Finish Date]@row <= TODAY(), Progress@row <> "Complete"), "Red", "Green")))

Dell55

hi I have a similar IF formula:

=IF([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row > 5, "yes", IF(AND([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row = 5, "hold", IF(AND([Seen this week ]@row + [Seen Last week]@row + [Seen Last 2 Weeks]@row + [Seen Last 3 Weeks]@row < 5, "no")))))

But its not working, its says #unparseable but I don't see where I went wrong

if its obvious to anyone please let me know

thanks!

Dell