Symbols using formulas

samantha.twynam99181

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?

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?