Calculate workdays, excluding weekends (Days elapsed)
I found the solution for "How to calculate days a ticket is open/closed" on your site (solution submitted by Andrée Starå), but, I need the formula to count just the weekdays, excluding weekends. I tried a couple of different ways, but cannot get it to work properly.
Any help would be greatly appreciated!
This is the formula that works, but I need to count just the weekdays
=IF([Project Completion Date]@row <> "", [Project Completion Date]@row - [Project Start Date]@row, IF([Project Start Date]@row <> "", TODAY() - [Project Start Date]@row))
Best Answer
-
Either of these should work. They just approach the logic from a different direction.
=IF([Project Completion Date]@row <> "", NETWORKDAYS([Project Start Date]@row, [Project Completion Date]@row), NETWORKDAYS([Project Start Date]@row, TODAY()))
=IF([Project Completion Date]@row = "", NETWORKDAYS([Project Start Date]@row, TODAY()), NETWORKDAYS([Project Start Date]@row, [Project Completion Date]@row))
Answers
-
You would incorporate the NETWORKDAYS function.
-
Hi Paul, thank you for the feedback, but, I don't want to "return the number of working days between two dates"
This is for Days Elapsed (no end date yet) . As you see in the above screenshot, there is no Project Completion Date, but it still counts the days Days Elapsed regardless.
The formula I show above works fine and I did notice that the Days Elapsed in my SmartSheet automatically increased by one day from yesterday to today, but I dont want to count weekend days.
Thank you again, hope you can help!
If you have a solution, could you show me how it would be written? I am not good at creating formulas, but I am good at finding them :) and adjusting accordingly, depending on how convoluted they are.
-
Right. Instead of Date minus Date you would use the NETWORKDAYS function. The other "date" inside of that can be TODAY.
So instead of saying [Completion Date] - [Start Date] or TODAY - [Start Date] depending on the true/false from the IF, you would say NETWORKDAYS between [Completion Date] and [Start Date] or NETWORKDAYS between TODAY() and [Start Date].
-
Thanks Paul, but I am completely lost. As I mentioned, I'm not good at writing formulas, I'm good at finding them and adjusting, but I still don't understand what this formula should look like. I tried your info from above but it's not working, as I know I'm not inserting the right info in the right context.
Thank you anyway
-
Either of these should work. They just approach the logic from a different direction.
=IF([Project Completion Date]@row <> "", NETWORKDAYS([Project Start Date]@row, [Project Completion Date]@row), NETWORKDAYS([Project Start Date]@row, TODAY()))
=IF([Project Completion Date]@row = "", NETWORKDAYS([Project Start Date]@row, TODAY()), NETWORKDAYS([Project Start Date]@row, [Project Completion Date]@row))
-
Thank you Carson, they both worked beautifully!
Woohoo!
After all these years of researching formulas for different scenarios and adjusting accordingly, this one had me stumped.
Thank you again!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives