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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
