Days remaining

I need help with my formula. It is only calculating if there is a completed date entered in the cell. I need it to calculate the number of days remaining and or number of days past the deadline, and I need it to show the calculations once a completed date is entered.

IF(ISDATE(Deadline@row), NETWORKDAY(Complete@row, Deadline@row), NETWORKDAY(TODAY(), Deadline@row))

Columns: Days Remaining, Task, Status, Deadline, Complete

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Are you able to provide a screenshot for context?

  • The first row does not have a complete date and is giving an error but the second has a complete date and is calculating. I need it to always calculate no matter if a complete date is entered.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I understand that part, but what does the rest of the sheet look like? What about the columns being evaluated?

  • Amber Eakin
    Amber Eakin ✭✭✭✭✭✭

    You're trying to calculate the days between the deadline and completion date (if there are both), and the deadline and today if there isn't a completion date. Is that right?

    Your formula is saying "if there is a date in the Deadline column" which will always be true; it's not giving parameters for what to do if there's not a date in the Complete column, which is why you're getting an error.

    Give this a go. This formula says "If the Complete cell is a date, then calculate the days between Complete and Deadline. If the Complete cell is not a date, calculate between the Deadline and today."

    =IF(ISDATE(Complete@row), NETWORKDAY(Complete@row, Deadline@row), NETWORKDAY(TODAY(), Deadline@row))

    Please let me know if that helps!

    Amber Eakin, MSLS, M.Ed.

    Adult Education Specialist | Process Improvement Enthusiast

  • Worked perfectly! Thank you

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!