# 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

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. 😀

• ✭✭✭✭✭✭
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!

• edited 07/12/24
Options

Thank you Dan for your help,

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

Bobbi

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!