Calculate workdays, excluding weekends (Days elapsed)

Seneca · 06/09/23

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))

Carson Penticuff · 06/12/23

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))

Paul Newcome · 06/09/23

You would incorporate the NETWORKDAYS function.

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

Seneca · 06/09/23

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.

Paul Newcome · 06/09/23

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

Seneca · 06/12/23

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

Carson Penticuff · 06/12/23

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))

Seneca · 06/12/23

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!

Smartsheet Basics

Smartsheet Basics

Calculate workdays, excluding weekends (Days elapsed)

Best Answer

Answers

Quick Links

Categories

Trending in Smartsheet Basics

Conversations in Sheet vs Report (for the Sheet)

Looking for Guidance on Smartsheet Aligned Solution Professional Certification

Conditional Formatting