Calculating a number of days from a start date but stop when there is an end date
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
-
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
-
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!
-
Thank you Dan for your help,
Entering both of those formulas come up with with #unparseable.
Bobbi
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!