# Calculate workdays, excluding weekends (Days elapsed)

Options
✭✭

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

You would incorporate the NETWORKDAYS function.

• ✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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!