# Formula to change status column based on % and date

Options

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!

• Overachievers Alumni
Options

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", "")

Kelly Drake (she/her/hers)

STARBUCKS COFFEE COMPANY| business optimization product manager

• edited 02/17/22
Options
• Options

I'm getting an "invalid operation

" 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", ""))))

• Overachievers Alumni
Options

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

Kelly Drake (she/her/hers)

STARBUCKS COFFEE COMPANY| business optimization product manager

• edited 02/17/22
Options
• Options

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", ""))))

Did this answer the question? Yes · No

• Overachievers Alumni
Options

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.

Kelly Drake (she/her/hers)

STARBUCKS COFFEE COMPANY| business optimization product manager

• Options

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.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!