Symbols using formulas

Hi there,

I am creating a training matrix, so I have employee names down the left and the training programs along the top. I want to set it so that if for example "WHMIS" expires after 1 year then the symbol would go from green to red 1 yr after the "Training Date" and 30 days before the expiry I would like it to go yellow.

Is this possible?

Find more posts tagged with

Formulas

Services

Comments

samantha.twynam99181

This might make it easier, I want these symbols to automatically change based on the set criteria

Mike Wilday

Okay, that is relatively easy to do but I noticed you are using Hold and No. In what cases would you use the yellow symbol and when would you use the red?

Mike Wilday

Here's how you would calculate the Green and Red based on a 1 year difference in the dates. You'll need to replace the Insert Date Column Name with whichever column name contains the date your testing. This will differ for each column. But what the formula does is add exactly 1 year to the training date.

=IF(TODAY() > DATE(YEAR([Insert Date Column Name]@row) + 1, MONTH([Insert Date Column Name]@row), DAY([Insert Date Column Name]@row)), "No", "Yes")

samantha.twynam99181

Oh! This is great!

So what I want it to do is:

Green if it is current (completed within that year)

Yellow (hold) if it is going to expire in 1 month (so 11 months from completion date)

Red if it has expired (1 year after completion date)

Mike Wilday

This one will check first for the year, then it will check for 11 months, and if both clear then it will be green. I'm not sure how it will address things if there are not dates in the Date Column... let me know if that's a necessity as well.

=IF(TODAY() > DATE(YEAR([Insert Date Column Name]@row) + 1, MONTH([Insert Date Column Name]@row), DAY([Insert Date Column Name]@row)), "No", IF(DATE(YEAR([Insert Date Column Name]@row), MONTH([Insert Date Column Name]@row)+11, DAY([Insert Date Column Name]@row)), "Hold", "Yes"))

samantha.twynam99181

It seems that if it's expired it works, if it has been 11 months since they completed its invalid and if its current its invalid

=IF(TODAY() > DATE(YEAR([Health and Safety Awareness - Employees]5) + 1, MONTH([Health and Safety Awareness - Employees]5), DAY([Health and Safety Awareness - Employees]5)), "No", IF(DATE(YEAR([Health and Safety Awareness - Employees]5), MONTH([Health and Safety Awareness - Employees]5) + 11, DAY([Health and Safety Awareness - Employees]5)), "Hold", "Yes"))

Mike Wilday

I found the issue. It didn't like me adding 11 months. I had to add a year and then subtract 1 month... to get a good date.

Try this.

=IF(TODAY() > DATE(YEAR([Health and Safety Awareness - Employees]5) + 1, MONTH([Health and Safety Awareness - Employees]5), DAY([Health and Safety Awareness - Employees]5)), "No", IF(DATE(YEAR([Health and Safety Awareness - Employees]5) + 1, MONTH([Health and Safety Awareness - Employees]5) - 1, DAY([Health and Safety Awareness - Employees]5)), "Hold", "Yes"))

Mike Wilday

Hmmm. It doesn't like that formula if the date is the first month of the year... it won't switch the year back when subtracting a month. This is going to be tricky.

Does anyone else in the community have any suggestions?

Sign in to join the conversation!

Quick Links

Trending discussions

seeking VLOOKUP or equivalent, search for most recent value, multiple dropdowns
My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown…
Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…