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

Bobbi Northam

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

Bobbi Northam

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

SoS | Dan Palenchar

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

https://help.smartsheet.com/function/netdays

Bobbi Northam

Thank you Dan for your help,

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

Bobbi

Bobbi Northam

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