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
-
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
-
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
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!