Change formula to only count network days

Hi All,

This formula works great to count the days which return a figure greater than 1 in a specific month / year. However I wish to remove the figures counted on weekends. I know I need to alter this to a network day formula but currently struggling to do so. Any help would be much appreciated.

=COUNTIFS({REGISTER DATE}, AND(IFERROR(MONTH(@cell), 0) = [Column3]@row, IFERROR(YEAR(@cell), 0) = LOCATION@row), {STAFF REGISTER}, >=1)

Find more posts tagged with

Accepted answers

All comments

KPH

Hi @Ed Gadd

The NETWORKDAYS function counts the number of working days between two dates. It doesn't look like you have two dates.

Do you want to change the COUNTIFS to only count rows where:

the month is the value in Colum3
the year is the value in LOCATION
the value in STAFF REGISTER is 1 or more
and the REGISTER DATE is on a Monday, Tuesday, Wednesday, Thursday, or Friday?

If so, I think the WEEKDAY function will be the one to use. You can add an extra criteria to your COUNTIF to include only rows where the WEEKDAY of the Registered Date is not Saturday (7) or Sunday (1).

Ed Gadd

Hi @KPH,

Yes I believe you are correct that the WEEKDAY function would be the correct approach. Would you have a suggestion of the formulas arrangement to only count on a weekday please?

KPH

Hi @Ed Gadd

You can add a second range and criteria pair to the COUNTIFS to only count those where the WEEKDAY is not 1 and the WEEKDAY is not 7.

Range: [REGISTER DATE]:[REGISTER DATE]
Criteria: AND(IFERROR(WEEKDAY(@cell), 0) <> 1, IFERROR(WEEKDAY(@cell), 0) <> 7)

The full formula would look like this:

=COUNTIFS([REGISTER DATE]:[REGISTER DATE], AND(IFERROR(MONTH(@cell), 0) = [Column3]@row, IFERROR(YEAR(@cell), 0) = LOCATION@row), [REGISTER DATE]:[REGISTER DATE], AND(IFERROR(WEEKDAY(@cell), 0) <> 1, IFERROR(WEEKDAY(@cell), 0) <> 7))

Ed Gadd

Hi @KPH,

Thank you very much for your help. I'll take a look at the formula and get it working today.

Thanks again, much appreciated.

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.