Auto checkbox formula when cell contains multiple values

Options

Hello!

I am trying to create a formula that automatically check the box if the "Year upgrade or addition" cell contains the year 2012 or later.


In my screenshot, you can see this cell contains the years 2007 and 2016. Since 2016 matches my criteria, I need the ADA Complaint? column to be checked.

I tried =IF([Year upgrade or addition]@row >= 2012, 1, 0), but that only worked if all the numbers in the column were 2012+.

I tried putting commas between the numbers, backslashes, double backslashes, and putting them on separate lines within the cell.

Answers

  • kelceyg
    kelceyg ✭✭✭✭✭
    Options

    After I posted this, I decided to go a different direction.

    I added a helper column to extract the latest date from the "Year upgrade or addition" column. Since the most recent upgrade is always the last year listed in the cell, I used this formula in my helper column: =VALUE(RIGHT([Year upgrade or addition]@row, 4))

    I was then able to create a formula for my checkbox column using the helper: =IF([Latest Upgrade]@row >= 2012, 1, 0)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!