Harvey Balls - two step rules

PM_Reeves

Hi,

As usual with SmartSheets, I don't think I'm too far off with this one, but I've hit a snag.

I've managed to get the Harvey Balls working properly for what I'd like on their own rows, but I'm trying to bring an extra function into play for Project Health.

I'd like to mark Projects in Blue for Completed. That seemed easy enough, but now I've been tasked with marking any uncomplete according to Date Slip.

Anything still < End Date to be Green, less than 14 days overdue to be Yellow, and over that to be Red.

Do I merely have the dates and status the wrong way round in the formula, and\or should Blue come first?

=IF([% Complete]34 < 1, "Green", IF(AND([End Date]34 <TODAY +14))[% Complete]34 < 1, "Yellow", IF((AND[End Date]34 >TODAY +14))[% Complete]34 < 0.99, "Red", IF([% Complete]34 = 1, "Blue"))))

btw, Row 34 is my overriding Project status

Thanks all, and I hope our US based S³ community enjoy Thanskgiving.

KPH

If only the formula just needed to be the right length and we could put whatever combination of commas and parenthesis wherever we wanted 😂

I thought that would be a quick question to answer but could not find all the issues without breaking it down, which then meant dashing off and leaving you hanging on the logic!

So, now back to your logic

I'd like to mark Projects in Blue for Completed.

Anything still < End Date to be Green, less than 14 days overdue to be Yellow, and over that to be Red.

The IF syntax is

logic, value if true, value if false

So when you nest IFs by replacing the value if false with another IF that IF will only be evaluated if the fist logic is false

So here, everything in bold will only be considered if % Complete is not <1.

=IF([% Complete]34 < 1, "Green", IF(AND([End Date]34 < TODAY(14), [% Complete]34 < 1), "Yellow", IF(AND([End Date]34 > TODAY() + 14, [% Complete]34 < 0.99), "Red", IF([% Complete]34 = 1, "Blue"))))

Which is not going to work for you.

You need to write your IF in the same way as you think:

If % Complete = 1 then Blue

If not 100% complete then

If end date is today or later (* I have guessed this is the intention here) then Green

If not (so if end date is before today)

and end date is later than 14 days before today* then Yellow

If not Red

=IF([% Complete]34 = 1, "Blue", IF([End Date]34 > TODAY(), "Green", IF(AND([End Date]34 > TODAY(-14), [% Complete]34 < 1), "Yellow", IF(AND([End Date]34 < TODAY(-14), [% Complete]34 < 0.99), "Red"))))

But I wouldn't use that....

Because % Complete can only be 1 or <1 (unless you have something allowing more than 100% complete) then you do not need to mention % complete again after setting up blue - everything will be not 1. Which makes the formula easier. Unless you did want Red to be based on <0.99?

And you can keep the Red part very simple as you will have ruled out the alternatives already (this also means you cope with an End Date exactly 14 days).

I would use this instead - Look how short it is! 😀 If it isn't quite the right logic, it should be easier to adapt this than the one above.

=IF([% Complete]34 = 1, "Blue", IF([End Date]34 >= TODAY(), "Green", IF([End Date]34 > TODAY(-14), "Yellow", "Red")))

This will mark in Red if blank, so you might need one more bit of logic.

KPH

Oh wow! You are a long way ahead of a colour word typed into a cell. 😮

I was joking about the length thing. You were so close. 🏆

Good luck!

KPH

I'm not sure what you mean by %age of time. But so long as you can display what you want with 5 symbols, and create an IF function to identify which symbol is the most appropriate that would be certainly be possible.

You can apply harveys to the difference between start and due date easily (more easily than what you were doing which included knowing today's date). For something like this (I've used two different icon sets as I prefer the bar for progress):

The formula in both Column6 and 7 is:

=IF([End Date]@row - [Start Date]@row < 7, "Empty", IF([End Date]@row - [Start Date]@row < 30, "Quarter", IF([End Date]@row - [Start Date]@row < 60, "Half", IF([End Date]@row - [Start Date]@row < 360, "Three Quarter", "Full"))))

This means if the difference between start and due is less than a week put an empty ball. If that is not true but the difference is less than 30 days, put a quarter, etc, etc, to if more than 360 put a full ball.

Slightly off topic - I don't like these icons to be used like this as the icons suggest each part is the same size which here they are not (60 days fills the first half, 300 days fills the second). But that isn't specific to smartsheet. I would only use these if you can split your projects into 5 similar sized bands.

KPH

Hi @PM_Reeves

There were a few pesky comma and parenthesis issues in that formula. I broke it into separate IF statements, fixed each one, then put it back together. The result is this, which is parseable but may not match your intended logic! If it works you can play with the logic.

=IF([% Complete]34 < 1, "Green", IF(AND([End Date]34 < TODAY(14), [% Complete]34 < 1), "Yellow", IF(AND([End Date]34 > TODAY() + 14, [% Complete]34 < 0.99), "Red", IF([% Complete]34 = 1, "Blue"))))

I'll also share the parts I messed with so you can see the changes

Green

Syntax is fine. Logic could be wrong.

Yellow

Original

=IF(AND([End Date]34 <TODAY +14))[% Complete]34 < 1, "Yellow")

Suggested

=IF(AND([End Date]34 < TODAY()+14, [% Complete]34 < 1), "Yellow")

Red

Original

= IF((AND[End Date]34 >TODAY +14))[% Complete]34 < 0.99, "Red")

Suggested - Either

=IF(AND([End Date]34 > TODAY(14), [% Complete]34 < 0.99), "Red")

or

=IF(AND([End Date]34 > TODAY() + 14, [% Complete]34 < 0.99), "Red")

PM_Reeves

Beautiful.

Didn't think I was far off, but I'd been looking at it for so long I just couldn't see where the issue was.

I mean, my attempt that failed is exactly the same length as yours which works 😀

Thanks again, 👍️

KPH

If only the formula just needed to be the right length and we could put whatever combination of commas and parenthesis wherever we wanted 😂

I thought that would be a quick question to answer but could not find all the issues without breaking it down, which then meant dashing off and leaving you hanging on the logic!

So, now back to your logic

I'd like to mark Projects in Blue for Completed.

Anything still < End Date to be Green, less than 14 days overdue to be Yellow, and over that to be Red.

The IF syntax is

logic, value if true, value if false

So when you nest IFs by replacing the value if false with another IF that IF will only be evaluated if the fist logic is false

So here, everything in bold will only be considered if % Complete is not <1.

=IF([% Complete]34 < 1, "Green", IF(AND([End Date]34 < TODAY(14), [% Complete]34 < 1), "Yellow", IF(AND([End Date]34 > TODAY() + 14, [% Complete]34 < 0.99), "Red", IF([% Complete]34 = 1, "Blue"))))

Which is not going to work for you.

You need to write your IF in the same way as you think:

If % Complete = 1 then Blue

If not 100% complete then

If end date is today or later (* I have guessed this is the intention here) then Green

If not (so if end date is before today)

and end date is later than 14 days before today* then Yellow

If not Red

=IF([% Complete]34 = 1, "Blue", IF([End Date]34 > TODAY(), "Green", IF(AND([End Date]34 > TODAY(-14), [% Complete]34 < 1), "Yellow", IF(AND([End Date]34 < TODAY(-14), [% Complete]34 < 0.99), "Red"))))

But I wouldn't use that....

Because % Complete can only be 1 or <1 (unless you have something allowing more than 100% complete) then you do not need to mention % complete again after setting up blue - everything will be not 1. Which makes the formula easier. Unless you did want Red to be based on <0.99?

And you can keep the Red part very simple as you will have ruled out the alternatives already (this also means you cope with an End Date exactly 14 days).

I would use this instead - Look how short it is! 😀 If it isn't quite the right logic, it should be easier to adapt this than the one above.

=IF([% Complete]34 = 1, "Blue", IF([End Date]34 >= TODAY(), "Green", IF([End Date]34 > TODAY(-14), "Yellow", "Red")))

This will mark in Red if blank, so you might need one more bit of logic.

PM_Reeves

Hi @KPH,

My length comment was in total surprise that I'd actually managed to get that close, and like you say, what I had originally just needed some parentheses and commas moving\deleting.

I'd been looking at it for hours, so all logic had left the building by then. 😅

Your explanation makes a lot of sense, but then again I've just had the first coffee...☕️

I'm taking over from what someone else had built, and their field for Health (which is what I'm using) merely referenced another cell which had a word typed in in a certain colour (no logic behind it at all) 😮

Thanks ever so much for your help, and even more for coming back and explaining.

Enjoy your day!

KPH

Oh wow! You are a long way ahead of a colour word typed into a cell. 😮

I was joking about the length thing. You were so close. 🏆

Good luck!

PM_Reeves

Hi @KPH , hope you had a great weekend.

Just wanted to run the feasibility of this one past you before I waste a shedload of time on something that isn't remotely possible in Smartsheet.

Is there a way to use Harvey Balls to indicate %age of time on specific projects? We're not really concerned with %age of completeness until it is complete, but some projects can take months, some a matter of days.

Would I be able to achieve something workable by using Start and Due Dates and applying Harveys to differences between those fields?

Cheers

KPH

I'm not sure what you mean by %age of time. But so long as you can display what you want with 5 symbols, and create an IF function to identify which symbol is the most appropriate that would be certainly be possible.

You can apply harveys to the difference between start and due date easily (more easily than what you were doing which included knowing today's date). For something like this (I've used two different icon sets as I prefer the bar for progress):

The formula in both Column6 and 7 is:

=IF([End Date]@row - [Start Date]@row < 7, "Empty", IF([End Date]@row - [Start Date]@row < 30, "Quarter", IF([End Date]@row - [Start Date]@row < 60, "Half", IF([End Date]@row - [Start Date]@row < 360, "Three Quarter", "Full"))))

This means if the difference between start and due is less than a week put an empty ball. If that is not true but the difference is less than 30 days, put a quarter, etc, etc, to if more than 360 put a full ball.

Slightly off topic - I don't like these icons to be used like this as the icons suggest each part is the same size which here they are not (60 days fills the first half, 300 days fills the second). But that isn't specific to smartsheet. I would only use these if you can split your projects into 5 similar sized bands.

PM_Reeves

Thanks again @KPH ,

way above and beyond. Just wondered if it was possible, yet you've done the whole thing.

You should definitely take tomorrow off.

👍️

KPH

😀 I had to prove I was telling the truth 😉

Actually, the %age complete thing threw me and I didn't want to say "sure, you can do that" and you to spend hours on it to realize that you couldn't and I'd completely misunderstood the ask. So I showed what you could do, in the hope it was what you wanted!