Connect with peers, share your expertise, and inspire what’s next in Smartsheet — from proven practices to practical how-to insights from fellow users and product experts.
Sign in to join the conversation:
Hi! Is there a way to count all tasks that are past due based off of date, but exclude tasks that are marked complete?
=COUNTIF(Finish:Finish, <TODAY())
Can I add an exemption in this so marked complete is not counted?
Please let me know!
Branden
Is Complete a checkmark field? Or a dropdown field? How, specifically, are tasks being marked as complete?
If its a checkmark,
=Countifs(Finish:Finish, < Today(), [Complete Column Title]:[Complete Column Title], 0)
If it's a dropdown try:
=Countifs(Finish:Finish, < Today(), [Complete Column Title]:[Complete Column Title], <>"Complete")
I am marking it complete with a checkbox. The goal here is to highlight any past due tasks by finish date that are not complete excluding tasks that have been marked complete. Then I would like to set a reminder within Smartsheet to automatically notify me via email or whatever that it is past due and to ensure we are staying on schedule.
So do you know how I can automatically do this and is the formula you mentioned previously the correct one?
Mike's formula of:
for the check boxes is the one you want.
You can then set up a notification to alert you whenever the date is in the past (or within x amount of days/weeks/etc. if you wanted to keep ahead of it) and add a condition within the same alert that the Complete Column is unchecked. Then it will only send you an alert (don't forget your Delivery Settings for timing and frequency) whenever BOTH of those criteria are met.
To highlight the rows in question you can use conditional formatting, setting the same criteria as the notification.
What Paul said.
Hi Everyone,
I am trying to create this formula in my Sheet but it says incorrect argument:
What I want to do, is to count how many tasks are overdue, excluding Complete Status.
Thank you for your Help.
Hi @Natalia Nino
Your first reference is one cell, but the second reference is an entire column. Based on your description I believe you want to have the entire column as a reference for the first part as well!
Try:
=COUNTIFS([Scheduled Finish Date]:[Scheduled Finish Date], <TODAY(), Status:Status, <> "Complete")
Cheers,
Genevieve
Hi Genevieve
Thank you for your help.
It works perfectly.
We set up a recurring Request for Update for each project when it starts. In the top row of the sheet, we use the feature to send an email at 8am every Friday morning. The past several months we have heard from various people each Friday they are missing one or more of these emails requesting an update. When we look at…
Hi all! I'm working on a column formula that will check whether the Column2ref cell in the current row is blank. If it is blank, the formula finds the maximum value in the ID column where the Trainer Name matches the Trainer Name cell value in the current row and the ID cell value is less than the ID cell value in the…
Hi Smartsheet Community, I'm working on an action management system where users create sub-actions through a Smartsheet Form. The requirement is simple: a sub-action's due date must be earlier than or equal to its parent action's due date. For example, if the parent action is due on June 30, 2027, users should not be able…
