Formula to count days, excluding weekends and holidays

Hello,

I am looking for a solution to the following problem;

[START DATE] - [COMPLETED DATE] = [DAYS]

I need the days column to calculate the days a project took...which seems simple enough, but I have a [holidays] column that I want to exclude from the count if the dates fall in this range and I need to exclude weekends.

I only want to count the working days.

I tried using the project settings to do this, but it messed up my sheet and is auto-populating the completed date. Not sure if i did it wrong.

Thanks for any help

Nat

Find more posts tagged with

Accepted answers

All comments

Nat O

Sorry i wrote that incorrectly, it should be [Completed Date] - [Start Date]

Andrée Starå

Hi @Nat O

I hope you're well and safe!

Try something like this.

=NETWORKDAY([START DATE]@row, [COMPLETED DATE]@row, [HOLIDAY DATES]:[HOLIDAY DATES])

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Nat O

Thanks Andree, it didnt quite do the trick. I believe the reason being, my holiday dates are not in line with each relevant entry. I am dealing with hundreds of rows and to work out if a holiday falls into each start and completion date would be quite laborious to do manually. The holiday dates are just a sitting in a column, not aligned to anything and i am hoping i can reference them, perhaps with a vlookup to exclude from the count?

This is sounding complicated as I write it out.

Nat

Andrée Starå

@Nat O

Happy to help!

It should work if you have all the dates in a Holiday column.

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Nat O

Thanks for persevering @Andrée Starå

It was easier to quickly replicate how it looks in excel . Does this help?

So the holidays are not "in-line" with the start and end dates they apply to, its just a list of dates i want to exclude.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.