Formula for lights

Hi all,

I have a sheet that contains fields for "Preparer Due Date" and "Preparer Status" and am trying to add in the following logic to a formula:

Blue = completed

Green = task not started or in process that are due more than 3 days away

Yellow = task not started or in process that are due within the next 3 days

Gray = task Not Started and not due within the next 3 days

Red = task not started or in process that are past due

Thanks in advance!

Find more posts tagged with

Formulas

Accepted answers

Bassam Khalil

@Brennan Montoni

Hope you are fine, please try the following formula and convert it to column format formula:

=IF([Preparer Status]@row = "completed", "Blue", IF(AND(OR([Preparer Status]@row = "not started"
, [Preparer Status]@row = "in Process"), [Preparer Due Date]@row > TODAY()), "Red", 
IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), 
[Preparer Due Date]@row < TODAY(-3)), "Green", IF(AND(OR([Preparer Status]@row = "not started", 
[Preparer Status]@row = "in Process"), [Preparer Due Date]@row >= TODAY(-3), 
[Preparer Due Date]@row <= TODAY()), "Yellow"))))

the following screenshot shows the result:

Bassam Khalil

@Brennan Montoni

Please try the following formula:

=IF([Preparer Status]@row = "completed", UNICHAR(128309), 
IF(AND([Preparer Status]@row = "not started", [Preparer Due Date]@row <= TODAY(3), 
[Preparer Due Date]@row > TODAY()), UNICHAR(128993), IF(AND([Preparer Status]@row = 
"not started", [Preparer Due Date]@row > TODAY(3)), UNICHAR(128280), 
IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), 
[Preparer Due Date]@row < TODAY()), UNICHAR(128308), IF(AND(OR([Preparer Status]@row = 
"not started",[Preparer Status]@row ="in Process"), [Preparer Due Date]@row > TODAY(3)), 
UNICHAR(128994))))))

The following screenshot shows the result:

Bassam Khalil

@Brennan Montoni

Please try the following formula to populate the yellow :

=IFERROR(IF([Preparer Status]@row = "completed", UNICHAR(128309), 
IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), 
[Preparer Due Date]@row <= TODAY(3), [Preparer Due Date]@row > TODAY()), 
UNICHAR(128993), IF(AND([Preparer Status]@row = "not started", 
[Preparer Due Date]@row > TODAY(3)), UNICHAR(128280), IF(AND(OR([Preparer Status]@row = 
"not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row < TODAY()), 
UNICHAR(128308), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = 
"in Process"), [Preparer Due Date]@row > TODAY(3)), UNICHAR(128994)))))), "")

All comments

Bassam Khalil

@Brennan Montoni

Hope you are fine, please try the following formula and convert it to column format formula:

=IF([Preparer Status]@row = "completed", "Blue", IF(AND(OR([Preparer Status]@row = "not started"
, [Preparer Status]@row = "in Process"), [Preparer Due Date]@row > TODAY()), "Red", 
IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), 
[Preparer Due Date]@row < TODAY(-3)), "Green", IF(AND(OR([Preparer Status]@row = "not started", 
[Preparer Status]@row = "in Process"), [Preparer Due Date]@row >= TODAY(-3), 
[Preparer Due Date]@row <= TODAY()), "Yellow"))))

the following screenshot shows the result:

Brennan Montoni

Thank you Bassam. This is great but I need the circle to be gray if the task is not started and not due within the next 3 days. If not started and due in the next 3 days, it should be yellow.

Bassam Khalil

@Brennan Montoni

Unfortunately in smartsheet symbol column you don't have this option to have 5 colored balls. maybe if you select different symbol shape you can have more options.

Andrée Starå

Hi @Brennan Montoni

I hope you're well and safe!

To add to Bassam's excellent advice/answer.

Here's a possible workaround or workarounds

You can use other symbols by using UNICHAR, CHAR, or Emojis.

More info:

https://help.smartsheet.com/function/unichar

https://help.smartsheet.com/function/char

https://getemoji.com/

A great resource that Paul Newcome put together

https://app.smartsheet.com/b/publish?EQBCT=82f8fe0936a54758a3afa4b584acde07

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bassam Khalil

@Brennan Montoni

Please try the following formula:

=IF([Preparer Status]@row = "completed", UNICHAR(128309), 
IF(AND([Preparer Status]@row = "not started", [Preparer Due Date]@row <= TODAY(3), 
[Preparer Due Date]@row > TODAY()), UNICHAR(128993), IF(AND([Preparer Status]@row = 
"not started", [Preparer Due Date]@row > TODAY(3)), UNICHAR(128280), 
IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), 
[Preparer Due Date]@row < TODAY()), UNICHAR(128308), IF(AND(OR([Preparer Status]@row = 
"not started",[Preparer Status]@row ="in Process"), [Preparer Due Date]@row > TODAY(3)), 
UNICHAR(128994))))))

The following screenshot shows the result:

Brennan Montoni

@Bassam Khalil

Hope all is well!

For some reason it is not populating a circle for this task which is in process and due in 3 days. I'd like to see this appear yellow if possible.

Thanks in advance!

Bassam Khalil

@Brennan Montoni

Please try the following formula to populate the yellow :

=IFERROR(IF([Preparer Status]@row = "completed", UNICHAR(128309), 
IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = "in Process"), 
[Preparer Due Date]@row <= TODAY(3), [Preparer Due Date]@row > TODAY()), 
UNICHAR(128993), IF(AND([Preparer Status]@row = "not started", 
[Preparer Due Date]@row > TODAY(3)), UNICHAR(128280), IF(AND(OR([Preparer Status]@row = 
"not started", [Preparer Status]@row = "in Process"), [Preparer Due Date]@row < TODAY()), 
UNICHAR(128308), IF(AND(OR([Preparer Status]@row = "not started", [Preparer Status]@row = 
"in Process"), [Preparer Due Date]@row > TODAY(3)), UNICHAR(128994)))))), "")

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.