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!

Find more posts tagged with

Accepted answers

All comments

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

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.