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 Wend Special_NetWkDays = numdays End Function
Usage in a cell:
=Special_NetWkDays(StartDay;EndDay;Holidays)
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