Formula to change status column based on % and date

Julie Hale-Harris

Smartsheet.PNG

I want a status column (red, yellow, green), to show up based on two hiring percentage and due date. I think I need to use an IF formula, but I can't seem to get the formula.

Red = less than 25% of goal hired, due date this week

Yellow = less than 50% hired , due date is more than 1 week out

Blue = less than 75% hired, due date is more than 1 week out

Green = 100% hired, and met due date.

Thank you!

Kelly Drake

If you want this week to be a rolling 7 days you could try this

= IF(Status@row = 1, "Green", IF(AND(Status@row = <.25, [Due Date]@row <= TODAY(7)), "Red", IF(AND(Status@row = <.5, [Due Date]@row <= TODAY(7)), "Yellow", IF(AND(Status@row = <.5, [Due Date]@row <= TODAY(7)), "Blue", "")

Julie Hale-Harris

Julie Hale-Harris

https://community.smartsheet.com/discussion/comment/320021#Comment_320021

I'm getting an "invalid operation

smartsheet formula error.PNG

" message when I entered that formula. Here's how it reads out.

=IF([Hired Percentage]@row = 1, "Green", IF(AND([Hired Percentage]@row = <0.25, Date@row <= TODAY(7)), "Red", IF(AND([Hired Percentage]@row = <0.5, Date@row <= TODAY(7)), "Yellow", IF(AND([Hired Percentage]@row = <0.5, Date@row <= TODAY(7)), "Blue", ""))))

Kelly Drake

Oh my bad! I put the < & = in the wrong order... flip them to be <= instead of =<

https://help.smartsheet.com/articles/2476176-formula-error-messages#toc--invalid-operation

Julie Hale-Harris

Julie Hale-Harris

https://community.smartsheet.com/discussion/comment/320032#Comment_320032

Gah, I must still have something wrong in the formula. Now it's saying "invalid data type".

=IF([Hired Percentage]@row = 1, "Green", IF(AND([Hired Percentage]@row, <=0.25, Date@row <= TODAY(7)), "Red", IF(AND([Hired Percentage]@row <= 0.5, Date@row <= TODAY(7)), "Yellow", IF(AND([Hired Percentage]@row <= 0.5, Date@row <= TODAY(7)), "Blue", ""))))

Smartsheet formula 2.PNG

Did this answer the question? Yes · No

Kelly Drake

Check your column Type.... the formula should work if you're using a Symbols column that is the dropdown for red, yellow, green & blue balls.

Julie Hale-Harris

https://community.smartsheet.com/discussion/comment/320045#Comment_320045

The status column is set up for the Symbols and 4 balls (Red, Yellow, Green and Blue), and it's still showing the same error message.