Formula to count days past due but leave the column blank if the task is completed
Hello,
On my sheet I have tasks that have a column for Due Date, Completion Date, and Checkbox for Done. I want to keep track of the tasks that are overdue and have an easy way to glance and see how many days they are over due from the due date. I have been successful in using this formula for that calculation
=TODAY() - [Due Date]@row
But using this it will calculate all my rows, even the ones that are completed. For now i've just used some conditional formatting to make the overdue ones red and the ones that are completed green, but i'd like to add to the formula to make it so that the ones that are marked complete don't put a number in the days past due column.
Formulas are not really my strongest area and i sort of just youtube and google my way through, but i am stumped on this one.
Thank you!
Best Answer
-
Try:
=IF(done@row=1, "", TODAY() - [Due Date]@row)
Change "done" to the name of your column if it's other than done.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers
-
Try:
=IF(done@row=1, "", TODAY() - [Due Date]@row)
Change "done" to the name of your column if it's other than done.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mark, thank you so much that was exactly right!!!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!