IF Function Question - Symbol changes with date

Hello,

I have been having some issues configuring a formula in smartsheets. I am trying to write a function where the Symbol (Green, Yellow, Red, Grey Balls) in the 'Status' column adjusts according to date in the 'Start' column.

Green appears if the start date is 7 days in the future or beyond

Yellow appears if the start date is today or within 7 days

Red appears if the start date is before today

Grey appears if the 'Done' column is checked

Please let me know if you can help. Do I copy the same formula into every cell? Do I change the statud column to Text while entering the formula?

New to all of this thank you,

Kelly

Find more posts tagged with

Accepted answers

Paul Newcome

Give this one a go...

=IF(ISDATE([Start Date]@row), IF(Done@row = 1, "Grey", IF([Start Date]@row < TODAY(), "Red", IF(Start Date]@row <= TODAY(7), "Yellow", "Green"))))

All comments

Paul Newcome

Try something like this...

=IF(Done@row = 1, "Grey", IF([Start Date]@row < TODAY(), "Red", IF(Start Date]@row <= TODAY(7), "Yellow", "Green")))

Once you enter this formula into one cell in the RYG column, you can use dragfill to quickly drag it down (or up) the rest of the column to fill in the rest of the cells.

KELLY ENNIS CONNELLAN

Thank you so much - this worked! Can you help me with what to add if I want nothing showing up if no date is entered?

Paul Newcome

Give this one a go...

=IF(ISDATE([Start Date]@row), IF(Done@row = 1, "Grey", IF([Start Date]@row < TODAY(), "Red", IF(Start Date]@row <= TODAY(7), "Yellow", "Green"))))

KELLY ENNIS CONNELLAN

So great -- thank you for your help!

Paul Newcome

Happy to help! 👍️

Simon Collier

Very helpful

Brittney Sinkfield

This worked for me, except that the words GREY, GREEN, YELLOW, and RED are showing up instead of the symbols/balls. How can I correct this?

Thanks in advance for any help! :)

Paul Newcome

@Brittney Sinkfield You will need to make sure the column type is set to Symbol and select the RYGG set of symbols. If I remember correctly, I also think that "Grey" is actually "Gray", and I do know that it is case sensitive. If the column type is already set properly, try changing the outputs to be an exact match to the colors including the case of each letter (first capital and rest lower).

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.