Formula to count days past due but leave the column blank if the task is completed



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

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓


    =IF(done@row=1, "", TODAY() - [Due Date]@row)

    Change "done" to the name of your column if it's other than done.


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!