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

07/13/21
Accepted

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!

Tags:

Best Answer

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    Try:

    =IF([email protected]=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

  • Mark CronkMark Cronk ✭✭✭✭✭
    Accepted Answer

    Try:

    =IF(done[email protected]=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!!!

  • SPRINGBOKSPRINGBOK
    edited 10/14/21


Sign In or Register to comment.