Formula to change status column based on % and date
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!
Answers
-
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
-
-
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", ""))))
-
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
-
-
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", ""))))
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!