Using IF, AND functions together.

Rachel Miranda

Hello,

I am trying to create a formula whereby, if the date in the "Due Date" column is less than today and if the "Approval Completed" column box is unchecked, return 1 or 0.

I need to calculate how many tasks are past the due date.

Unfortunately I am getting errors in my formula, can anyone help.

Thanks.

Genevieve P.

Hi Rachel,

Try this:

=IF(AND([Due Date]@row < TODAY(), [Approval Completed]@row = 0), 1, 0)

This would go in a helper column, drag-filled down the whole sheet so that each row indicates if the Due Date is in the past and the Completed box is not checked. Is that what you were looking to do?

If you just wanted the total number without indicating which specific row met that criteria, you could use a COUNTIFS function to return the overall count:

=COUNTIFS([Due Date]@row < TODAY(), [Approval Completed]@row = 0)

Here are some Help Articles I used: @row Function / AND function / TODAY function / COUNTIFS function

Let me know if you need any further help or clarification!

Cheers,

Genevieve

Paul Newcome

Which do you want it to return? 1 or 0?

=IF(AND([Approval Completed]@row <> 1, [Due Date]@row < TODAY()), 1)

or

=IF(AND([Approval Completed]@row <> 1, [Due Date]@row < TODAY()), 0)

Genevieve P.

Hi Rachel,

Try this:

=IF(AND([Due Date]@row < TODAY(), [Approval Completed]@row = 0), 1, 0)

This would go in a helper column, drag-filled down the whole sheet so that each row indicates if the Due Date is in the past and the Completed box is not checked. Is that what you were looking to do?

If you just wanted the total number without indicating which specific row met that criteria, you could use a COUNTIFS function to return the overall count:

=COUNTIFS([Due Date]@row < TODAY(), [Approval Completed]@row = 0)

Here are some Help Articles I used: @row Function / AND function / TODAY function / COUNTIFS function

Let me know if you need any further help or clarification!

Cheers,

Genevieve

Paul Newcome

Which do you want it to return? 1 or 0?

=IF(AND([Approval Completed]@row <> 1, [Due Date]@row < TODAY()), 1)

or

=IF(AND([Approval Completed]@row <> 1, [Due Date]@row < TODAY()), 0)

Rachel Miranda

Thank you so much Paul. This is exactly what I need.

Can you explain further to help me for the next time

What does " <> 1 mean

Paul Newcome

Checkboxes use 1/TRUE for checked and 0/FALSE for unchecked.

<> 1 simply means "does not equal 1" or is not checked. On rare occasion, I have had issues with using "= 0" or "equal unchecked", so I have just gotten in the habit of using <> 1.