IF statements and checkboxes

Mahshad Farmanbar

Hi there,

Currently I'm using this formula to check boxes in a column if the date a proposal is sent is in 2019.

IF(AND([Date Proposal Sent]534 > DATE(2018, 12, 31), [Date Proposal Sent]534 < DATE(2019, 12, 31))

Sometimes there is no date value for "Date Proposal Sent" and the cell says "In Pending". This causes my checkboxes to say "Invalid Operation". How can I revise my formula so that if the "Date Proposal Sent" is "In Pending" that the checkbox is simply not marked off?

Thanks in advance!

Mahshad

Andrée Starå

Hi Mahshad,

Try something like this.

=IF(ISTEXT([Date Proposal Sent]@row); 0; IF(AND([Date Proposal Sent]@row > DATE(2018; 12; 31); [Date Proposal Sent]@row < DATE(2019; 12; 31)); 1; 0))

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

=IF(ISTEXT([Date Proposal Sent]@row), 0, IF(AND([Date Proposal Sent]@row > DATE(2018, 12, 31), [Date Proposal Sent]@row < DATE(2019, 12, 31)), 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 or 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!

Andrée Starå

Hi Mahshad,

Try something like this.

=IF(ISTEXT([Date Proposal Sent]@row); 0; IF(AND([Date Proposal Sent]@row > DATE(2018; 12; 31); [Date Proposal Sent]@row < DATE(2019; 12; 31)); 1; 0))

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

=IF(ISTEXT([Date Proposal Sent]@row), 0, IF(AND([Date Proposal Sent]@row > DATE(2018, 12, 31), [Date Proposal Sent]@row < DATE(2019, 12, 31)), 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 or 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!

Paul Newcome

You can also use:

=IFERROR(IF(YEAR([Date Proposal Sent]@row) = 2019, 1), 0)

Basically we just use the YEAR function to pull the year from the date instead of having to specify a date range.

=IF(YEAR([Date Proposal Sent]@row) = 2019, 1)

Then to account for no date being present throwing an error, we just wrap it in an IFERROR statement to replace the error with a 0.

=IFERROR(IF(YEAR([Date Proposal Sent]@row) = 2019, 1), 0)