Change symbol based on date

Good morning Everyone,

I have been having some issues configuring a formula in smartsheets. Currently the only two columns that matter are named "Status" and "Last Run". What I'm trying to write is a function in which...

Green appears if the last run date is within 30 days

Yellow appears if last run is within 31-60 days

Red appears if last run is 61 or more days

Can anyone help me write this formula? Thank in advance.

Find more posts tagged with

Human Resources

Sheets

Formulas

Accepted answers

All comments

Paul Newcome

=IF([Last Run]@row < TODAY(-60), "Red", IF([Last Run]@row < TODAY(-30), "Yellow", "Green"))

Give this a go...

CowGuy27

Thank you so much Paul, Just one last quick question. I would like to start getting better at basic programming of IF/Then statement and other such functions in smartsheets and Excel. Could you recommend any classes, books, online courses that would be beneficial in this regard? Thanks again for the functions code it works perfectly now.

Paul Newcome

I am pretty much self taught through trial and error, but I believe there may be some videos and whatnot in the Learning Center.

Outside of that, I'm sure some Google searches and YouTube videos are out there as well.

The best advice I can offer:

1. Use your resources.

I very strongly recommend downloading the template called Smartsheet Formula Examples from the Solution Center. It is an interactive listing with examples of every function Smartsheet has to offer. If you start experimenting with it and accidentally save a mistake, you can just delete the sheet and download a new copy from the Solution Center. I do suggest regularly downloading a fresh copy just to be sure you have the most updated version.

HERE is a webpage with similar info.

And HERE is a page that lists all of the possible errors, the reason(s) for them, and some troubleshooting tips.

And last but certainly not least, the Community has really helped me a lot.

2. Patience. Lots and lots and lots of patience.

3. Think big small big. Here's an example:

Look at the big picture first.

Q: What is the overall goal?

A: To automatically generate a person's email address based off of their name being in another column.

Now think a little smaller.

Q: What functions can be used to display data?

A: Look through the Functions listing and see which ones can do that basic task.

A little more detail...

Q: How many different people will I be autopopulating an email for?

A: 25

Narrow it down a little more...

Q: A nested IF statement for 25 different email addresses would be HUGE, inflexible, and very prone to error. What can I use to pull data from a table/database instead?

A: INDEX and VLOOKUP both can pull data from a table.

Decision time...

Q: Which one will I use?

A: This would be your own personal preference, but I recommend the INDEX as it is much more flexible.

Break the function down.

Q: What are the different parts to the INDEX function?

A: There are three.

1. The range you want to pull data from.

2. Row number.

3. Optional Column number.

Even smaller now...

Q: What is the range I want to pull data from?

A: Easy. The Email column from my Directory table.

=INDEX([Directory Email Column]:[Directory Email Column],

Next...

Q: What row number do I want to pull the data from in my Email column? I don't want to manually adjust the formula every time I use a different name, so what functions can automatically generate a number?

A: A lot of them can, but we know that we are searching based on a name (data in another column). We can use that to narrow down our list.

Another decision...

Q: So which one do we want to use based on what we know? If "John Smith" is the third name down on the directory, how do we automatically generate a number 3 for the INDEX function?

A: The MATCH function will generate a number based on where within a table specific data (in this case a name) is found.

Break the function down.

Q: What are the different parts to the MATCH function?

A: There are three in this one as well.

1. What are we looking for?

2. Where are we looking?

3. What order is our list in?

Smaller again...

Q: What are we looking for?

A: The name in our Name Column.

=MATCH([Name Column]@row,

Next...

Q: Where are we looking for the name?

A: In the name column of our Directory.

=MATCH([Name Column]@row, [Directory Name Column]:[Directory name Column],

Finally...

Q: How is our list sorted?

A: We are going to go with 0 (zero). This part is optional, but I have found that this provides the most accurate results.

=MATCH([Name Column]@row, [Directory Name Column]:[Directory name Column], 0)

Next step....

Q: Ok. So we figured out how to automatically pull a row number for our INDEX function using a MATCH function. Now what?

A: Put the entire MATCH function in the second portion of the INDEX function.

=INDEX([Directory Email Column]:[Directory Email Column], MATCH([Name Column]@row, [Directory Name Column]:[Directory name Column], 0)

On to the third (and final) portion of the INDEX statement.

Q: Which column number do we need?

A: This portion is optional. If you are only looking to pull data from one column, we don't need to specify a column number. So we just close out our INDEX function.

=INDEX([Directory Email Column]:[Directory Email Column], MATCH([Name Column]@row, [Directory Name Column]:[Directory name Column], 0))

And there you have it. The same thought process can be used when nesting any functions including a nested IF statement.

=IF(this is true, then do this, otherwise do this)

But we want to say that if this is NOT true, but if that is true, then do that, otherwise just leave the cell blank.

So we have two IF statements

1. =IF(this is true, do this, if not then do the second IF)

2. =IF(that is true, do that, if both are not then leave blank)

Just drop that second IF into the third "otherwise" portion of the first (underlined).

=IF(this is true, do this, IF(that is true, do that, if both are not then leave blank))

It may seem like a long and drawn out process while reading through this, but it happens much faster when you are actually doing it. The process will also become much faster and intuitive with experience.

Another tip with this process is to spread each function out into it's own cell for testing and troubleshooting while building out the more complex ones. This will allow you to really fine tune and tweak each portion individually and work out all of the bugs before putting it all together into a larger formula.

The last bit of advice I can give you goes hand in hand with Patience, but definitely needs to be specified...

Ensure you have a large stock of pain killers and coffee, and if you haven't already, learn some basic handyman skills.

You are going to beat your head against a wall quite a bit. Even those of us that are pretty proficient in formula building still do it.

You are going to want to learn how to fix said walls (and mashed keyboards), and don't be surprised if your breakfast becomes headache meds washed down with coffee. Hahaha.

Seriously though... Best of luck to you, and don't hesitate to ask the Community. Most of us are more than happy to help when we can.

22Wordsmith

https://community.smartsheet.com/discussion/comment/190206#Comment_190206

@Paul Newcome this is very helpful but it returns the words. How would I make the formula return a coloured bullet point, please? I initially thought to use the symbols but can't enter a formula in those cells.

Andrée Starå

Hi @22Wordsmith

I hope you're well and safe!

You should be able to add the formula to a Symbol type column, and then it should work.

Did 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!

Paul Newcome

@22Wordsmith Did you double check that the column you are putting this formula in is set as the appropriate Symbol type column?

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.