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

Find more posts tagged with

Accepted answers

Carson Penticuff

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

All comments

Paul Newcome

You would incorporate the NETWORKDAYS function.

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

Seneca

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

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

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

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

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.