Auto checkbox formula when cell contains multiple values

kelceyg
kelceyg โœญโœญโœญโœญโœญ

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.

2023-11-06_15-06-27.jpg


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 โœญโœญโœญโœญโœญ

    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!