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?

Capture.PNG

Comments

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

    Capture.PNG

  • Mike Wilday
    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
    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")

     

  • 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
    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"))

  • 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"))

    Capture.PNG

  • Mike Wilday
    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
    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? 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!