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

Mahshad Farmanbar

Hello,

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!

Thanks

Andrée Starå

Hi Mahshad,

@Mahshad Farmanbar

Try something like this.

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

The same version but with the below changes for your and others convenience.

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

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

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)

Mahshad Farmanbar

Hi Mike,

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

Andrée Starå

Hi Mahshad,

@Mahshad Farmanbar

Try something like this.

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

The same version but with the below changes for your and others convenience.

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

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

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.

Samantha Rice

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

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

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

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.