Using IF, AND functions together.
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.
Best Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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)
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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)
-
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
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!