Formula Help

ShannonO

Hello

New user needing help with formula.

I would like it to be something like this:

When the due date is => than today -1 turn red, if the due date is < today - 3 days turn yellow, if due date is is =. than today +5 turn green

The idea is if a task is due today or tomorrow its red, if a task is due within 3-5 days its yellow and if a task is due in more than 5 days turn green.

Now i know how to use conditional formatting to get my colors. I'm jut not 100% how to set up the formula.

Any knowledge you have will be wonderful. Thank you. 

 

Jessica Bradbury

Conditional formatting rules would look like this & need to be in this order to work the way you'd like. With Smartsheet rules, they work in order. If the 1st rule doesn't apply to the row, it checks the second, if that doesn't apply to the row it checks the 3rd... and so on. Hope this helps. 

Mike Wilday

To do this with conditional formatting you will need to use a helper column. I would set up a new column called formatting. In it, you would add the following formula. Then in conditional formatting you will look for Red, Yellow, or Green in the helper column. You could also use the Symbol column with RYG symbols as well. 

=IF([Due Date]12 - 1 <= TODAY(), "Red", IF([Due Date]12 - 4 <= TODAY(), "Yellow", IF([Due Date]12 - 5 >= TODAY(), "Green")))

I adjusted the formula a bit to make it work like you are thinking about. Give it at ry and see if it works. This formula expects 2 things. 1) that your due date column is called Due Date, and that this formula is put in row 12. 

Let me know if this is what you were looking for. 

rbogartz

Hi, I was working on another version that calculates the health based on the percentage days left compared to the percentage the task is complete. For example if you have a task that is slated to take thirty days and starts on June 1st, then by June 15th, the expected completion percentage would be 50%. If that percentage was less than 50, then the health would show up as yellow. If it was 50 or greater than it would be green. If the end date was greater than the current date, then it would be red. If at any time the % complete is 100, it always shows up green. If the % complete was empty or 0, then it would not pick a color and just say "Not Started".

Here is the formula I used:

=IF([% Complete]2 = 0, "Not Started", IF([% Complete]2 = 1, "Green", IF(TODAY() > [End Date]2, "Red", IF(1 - (NETDAYS(TODAY(), [End Date]2)) / (NETDAYS([Start Date]2, [End Date]2)) >= [% Complete]2, "Yellow", "Green"))))

So..."2" in the formula, as in ([% Complete]2, indicates the row. 

For this formula to work you'll need a start date, end date, % Complete (number in the form of a percentage) and a "Health" column that uses a red/yellow/green symbol.

Then all you'd need to do is, for each task, enter a start date, an end date and the % Complete value and the red/yellow/green assignment is filled in. The only issue with this approach is that it is strictly linear. If you have a task where 80% of the work that needs to be done is done in the last 10% of the time allotted, then it will appear yellow most of the time.

Mike Wilday

Sometimes, us geeks make things much harder then they should be. Jessica's solution is actually the cleanest.

Richard Rymill SBP

Nice one Jessica and well said Mike. 

"KISS" ( Keep it simple) 

RichardR  

ShannonO

Ahhhh, bless you. It worked. 

Mike Wilday

Diplomatically spoken Richard.  

• KISS (Keep it simple)

rbogartz

I'm new to this forum and I'd like to say that I did not feel welcomed at all by these replies. I found these replies to be condescending and insulting. I'm going to guess that that was not anyone's intent but when you say that my contributions were "geek" heavy or use another term like KISS (and the last "S" means Stupid by the way), it struck me as very insulting and far from diplomatic.

I would have thought this was a forum to share knowledge and not place values or make judgements.

Not a good start.

Mike Wilday

Hi Rbogartz, 

I am afraid that we were speaking about ourselves. Not about any other users. My statement about how, "sometimes us Geeks make things harder than they need to be," sparked Richard's comment about "us geeks." It was not directed at anyone but ourselves. Anytime you have questions, and we can help, I have always seen the community respond. Welcome to Smartsheets and don't be afraid to ask any questions. We love to help!