Calculating a number of days from a start date but stop when there is an end date

Options

Hi,

I have a tracker sheet for applications that come in and we need to be able to calculate the days when they come on hold. There is a formula already for when it first comes on hold being =TODAY() - [On-Hold 1 (Start Date)]@row but when we receive the correct information we need to take it off hold and would like to the number of days to stay on that Total. Adding to the above formula is not working for me, so I think I am doing something wrong.

Can anyone help with the appropriate formula that would work.

Thank you

Bobbi

Best Answer

  • Bobbi Northam
    Answer ✓
    Options

    Tried the AI Generate as I had a bit of time to try and figure it out. Works a treat. Came up with this

    =IF(ISBLANK([On-Hold 1 (Start Date)]@row), "", IF(ISBLANK([On-Hold 1 (Finish Date)]@row), NETDAYS([On-Hold 1 (Start Date)]@row, TODAY()), NETDAYS([On-Hold 1 (Start Date)]@row, [On-Hold 1 (Finish Date)]@row)))

    Crisis diverted. 😀

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    Hello @Bobbi Northam,

    Since you have start and finish dates for on hold, you could use an IF() statement that returns the number of days between these two values if there is indeed a finish date, and if not, uses TODAY(). Try the below formula in column On Hold 1 Total.

    =IF(ISDATE([On-Hold 1 (Finish Date)@row), NETDAYS([On-Hold 1 (Start Date)]@row, [On-Hold 1 (Finish Date)@row), NETDAYS([On-Hold 1 (Start Date)@row), TODAY())

    If you want to make this a column formula I think the below will work (but I am doing this off the top of my head so not certain)

    =IFERROR(IF(ISDATE([On-Hold 1 (Finish Date)@row), NETDAYS([On-Hold 1 (Start Date)]@row, [On-Hold 1 (Finish Date)@row), NETDAYS([On-Hold 1 (Start Date)@row), TODAY()), "")

    Check out the NETDAYS() function as it may help you in situations like this

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • Bobbi Northam
    edited 07/12/24
    Options

    Thank you Dan for your help,

    Entering both of those formulas come up with with #unparseable.

    Bobbi

  • Bobbi Northam
    Answer ✓
    Options

    Tried the AI Generate as I had a bit of time to try and figure it out. Works a treat. Came up with this

    =IF(ISBLANK([On-Hold 1 (Start Date)]@row), "", IF(ISBLANK([On-Hold 1 (Finish Date)]@row), NETDAYS([On-Hold 1 (Start Date)]@row, TODAY()), NETDAYS([On-Hold 1 (Start Date)]@row, [On-Hold 1 (Finish Date)]@row)))

    Crisis diverted. 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!