Change formula to only count network days

Ed Gadd

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)

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.