Formula

Options
chawkins
chawkins
edited 08/29/22 in Formulas and Functions

I need assistance with a formula. I need to track the number of remaining business days until a project "Due Date," but once a "Completion Date" has been entered, I want that number of days to stop counting so that I can see how many days the project was over due.

This is what I have so far:

=IF(Status@row = "Complete", [Due Date]@row - [Completion Date]@row, [Due Date]@row - TODAY())

Answers

  • Marvin Daniels
    Marvin Daniels ✭✭✭✭✭
    Options

    You will need another column - Completion Date. The new formula will test that column for a date, if there a date, then it will calculate using Completion Date, not Due Date. Something like this:

    =IF(ISDATE([Completion Date]@row), NETWORKDAYS([Completion Date]@row]@row, TODAY()), NETWORKDAYS([Due Date]@row, TODAY()))

  • chawkins
    Options

    So I entered this formula:

    =IF(ISDATE([Completion Date]@row), NETWORKDAY([Completion Date]@row, [Due Date]@row), NETWORKDAY(TODAY(), [Due Date]@row))

    Due Date: 8/24/22

    Completion Date: 8/24/22

    And my answer is "1"


    I have played with the dates and you cannot get the "Remaining Days" to equal zero.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!