I'm trying to create a checkbox that will be selected when two criteria are met


I'm trying to create a checkbox that will be selected when two criteria are met.

The criteria that would all need to be met for the checkbox to be selected would be

1) An end Date no more than 90 days away

2) Status that is "In Progress"

The formula I have so far is:

IF((AND([End Date]1 <= TODAY(90), [Status]1, "In Progress")) 1, 0)

But it is coming up as #UNPARSEABLE. Any help with this would be greatly appreciated!


Best Answer


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You have a lot of extra parentheses in there. And you were missing a comma after the ANd statement.

    Try this simple revision.

    IF(AND([End Date]1 <= TODAY(90), [Status]1, "In Progress"), 1, 0)

  • Hi Mike,

    Thanks for the response! I've tried your solution however I get the error "Invalid Data". Any idea what this may be?

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Try Andree's revision and also try removing the brackets from around [Status] as you only have to have brackets around column names with spaces or that end in numbers.

  • I am having a similar issue, but I can't get get my formula to work. I am trying to get a box to check when the date exceeds or is equal to 6 months (180 days) and when another box is checked. This is what I have, but I think it's the checkbox condition that's throwing things off:

    =IF(AND([Start Date]@row >= TODAY(180), [Uniformed Employee]@row = "1"), 1, 0)

    For the Uniformed Employee checkbox, I have tried removing the quotations, changing the equal sign to a comma, changing the 1 to greater than 0. Please help!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Can you share a screenshot of the data masking any sensitive information?

    The formula looks legitimate.

    Based on your formula you are looking for any start date that is equal greater than today + 180 days. That's greater than 180 days into the future. Not within the next 180 days. I would do something like this.

    =IF(AND([Start Date]@row <= TODAY(), [Start Date]@row >= TODAY(-180), [Uniformed Employee]@row = 1), 1, 0)

    That would look for anything that is today or in the past 180 days. Put a restriction in time on the item and definitely remove the quotes.

    Try that, but if you get a different error let me know.

  • Samantha Rice
    Samantha Rice ✭✭✭
    edited 11/13/20

    Yes, I am looking for dates greater than 180 days. I am not getting an error message, but the checkbox column the formula is in isn't working. Both of these criteria are met in my sample row (the start date was in 2008, and the second box is checked) and the box is not checking.

    EDIT: Someone else helped in another thread as well. The problem has been solved!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    So if you want to look in the past you'll want to use the < less than symbol and use a minus days.

    Try this: =IF(AND([Start Date]@row <= TODAY(-180), [Uniformed Employee]@row = 1), 1, 0)

    This should work for you.

    For reference =Today(-180) has a May 2020 date and =Today(180) has a May 2021 date.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!