Being able to count networkdays by month in a range that covers two months!

Hi there

I'm trying to create a calculation of a date range... for example

So I have a networkdays formula counting the working days between the start date and end date. However, what I'm looking for is a formula that will count the network days for each month, for example above, June in a column, and the July dates in another column.

Any help would be greatly appreciated!

Many thanks

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Would it ever need to span more than two months?

Jo Petch

Hi Paul

No it wouldn't.

Thank you

Paul Newcome

Ok. Give this a try...

First Month:

=NETWORKDAYS([Start Date]@row, IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + 1, 1), DATE(YEAR([Start Date]@row) + 1, 1, 1)) - 1)

Second Month:

=NETWORKDAYS(DATE(YEAR([End Date]@row), MONTH([End Date]@row), 1), [End Date]@row)

Jo Petch

Thank you so much but unfortunately it didn't work.

It is counting the network days left in the rest of the month, not the network days between the date range. What I'm trying to achieve is a total count of all employees leave separated by month.

Thank you so much for trying though.

Best wishes

Paul Newcome

Right. You need two separate columns. One for the number of days in the first month and another for the number of days in the second month.

The number of days in the first month would be the remaining number of days in the month.

Donovan

Hi @Paul Newcome

Nice solution!, I need something similar but separating the qty of NETWORKDAYS into the calendar year across multiple years. How do i need to modify this to make it work?

Thanks

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.