NETWORKDAYS without 2nd & 4th Saturday

Sometimes, for some employees, not all Saturdays are free 🙁


The presented below UDF to calculate NETWORKDAYS including Holidays list, but counting as working day every 2nd and 4th Saturday of the month was inspired by the request from the forum (unanswered for quite some time).

The applied algorithm is pretty strightforward – for each day in examined period check if it is Sunday, Holiday or Saturday  (but not 2nd or 4th). If not add 1 to count and examine next day.

The only optimization applied is that 2nd and 4th Saturdays dates are calculated only once each month. See the code below:

Function Special_NetWkDays(StartDay As Date, EndDay As Date, Holidays As Range)
Dim current As Date, numdays As Integer, currMnth As Integer, Sat2 As Date, Sat4 As Date, numSatsofat As Integer
current = StartDay
While current <= EndDay
  If Month(current) <> currMnth Then
    Sat2 = DateSerial(Year(current), Month(current), 14 - Weekday(Application.EoMonth(current, -1)))
    Sat4 = 14 + Sat2
    currMnth = Month(current)
  End If
  If Not (Weekday(current) = 1 Or Application.CountIf(Holidays, current) <> 0 _
   Or (Weekday(current) = 7 And current <> Sat2 And current <> Sat4)) Then
    numdays = numdays + 1
    Debug.Print current
  End If
  current = current + 1
Special_NetWkDays = numdays
End Function

Usage in a cell:


Why different approach than original array formula:

=NETWORKDAYS(StartDay,EndDay,Holidays)+SUM((MOD(ROW(INDEX(A:A,StartDay):INDEX(A:A,EndDay)), 7)=0)* (MOD(WEEKNUM(ROW(INDEX(A:A,StartDay):INDEX(A:A,EndDay)))-WEEKNUM(EOMONTH(ROW(INDEX(A:A,StartDay):INDEX(A:A,EndDay)),-1)+1)+1,2)=0))

It would be not so easy to add to this formula next condition – no work on 2nd or 4th Saturday if it is a Holiday. See what I mean in provided sample file: SpecialNetWorkDays

Leave a Reply