Count working days with 2nd and 4th Saturdays off in Excel

Where this count is used

Attendance and payroll need the number of working days in a month to calculate loss-of-pay deductions. Service-level reports count working days between a ticket being raised and being closed. HR counts working days in a notice period, and project teams plan capacity by month. In each case, "working day" means what your office calendar says, which in many Indian offices is not Monday to Friday or Monday to Saturday.

The Excel setup

Create a sheet called Calendar with one column of off-dates, formatted as a table named OffDays. It holds two kinds of rows: generated second and fourth Saturdays, and the holidays your company declares for the year.

In Microsoft 365, generate the Saturdays for a whole calendar year with a single formula. With the year in B1:

=LET(d, SEQUENCE(DATE(B1,12,31) - DATE(B1,1,1) + 1, 1, DATE(B1,1,1)),
     n, INT((DAY(d) - 1) / 7) + 1,
     FILTER(d, (WEEKDAY(d, 2) = 6) * ((n = 2) + (n = 4))))

WEEKDAY(d, 2) numbers Monday as 1, so Saturday is 6. Copy the spilled dates as values into the table, then paste the company holiday list below them. Holidays that fall on a Sunday or an off-Saturday can stay in the list; NETWORKDAYS.INTL does not subtract a day twice.

Then the count for any range is:

=NETWORKDAYS.INTL(A2, B2, "0000001", OffDays[Date])

The weekend string has seven characters, Monday first, with 1 marking a weekend day. "0000001" is Sunday only; "0000011" would make every Saturday off as well. For a financial year that runs April to March, generate the Saturdays for both calendar years involved, since January to March belongs to the next calendar year.

For older Excel without SEQUENCE and FILTER, list every date of the year in one column, add a helper column with =AND(WEEKDAY(A2,2)=6, OR(INT((DAY(A2)-1)/7)+1=2, INT((DAY(A2)-1)/7)+1=4)), filter for TRUE and copy the dates.

Test the rule before payroll depends on it

A working-day count is wrong silently: the formula returns a plausible number either way. The Python below implements the same rule independently and checks it on months where the answer can be counted by hand. The holiday list is illustrative; your office list is the one that counts.

from datetime import date, timedelta

def nth_saturdays(year, month, which=(2, 4)):
    """Dates of the 2nd and 4th Saturdays: a Saturday is the nth when (day - 1) // 7 + 1 == n."""
    d, out = date(year, month, 1), []
    while d.month == month:
        if d.weekday() == 5 and (d.day - 1) // 7 + 1 in which:
            out.append(d)
        d += timedelta(days=1)
    return out

def working_days(start, end, holidays=()):
    """Inclusive count, like NETWORKDAYS.INTL: Sundays off, 2nd/4th Saturdays off, plus holidays."""
    off = set(holidays)
    d = date(start.year, start.month, 1)
    while d <= end:                                   # collect off-Saturdays for every month touched
        off.update(nth_saturdays(d.year, d.month))
        d = date(d.year + (d.month == 12), d.month % 12 + 1, 1)
    n, d = 0, start
    while d <= end:
        if d.weekday() != 6 and d not in off:
            n += 1
        d += timedelta(days=1)
    return n

# August 2026 has five Saturdays: 1, 8, 15, 22 and 29. Only the 8th and 22nd are off.
print("Aug 2026 off-Saturdays:", nth_saturdays(2026, 8))
assert nth_saturdays(2026, 8) == [date(2026, 8, 8), date(2026, 8, 22)]

# Office holiday list is the company's own; 15 August is Independence Day.
# In 2026 it falls on the third Saturday, which is a working day, so it does reduce the count.
office_holidays = [date(2026, 8, 15)]
aug = working_days(date(2026, 8, 1), date(2026, 8, 31), office_holidays)
print("Aug 2026 working days:", aug)
assert aug == 31 - 5 - 2 - 1                          # 5 Sundays, 2 off-Saturdays, 1 holiday

# A holiday that lands on a day already off must not be subtracted twice.
double = working_days(date(2026, 8, 1), date(2026, 8, 31), [date(2026, 8, 22), date(2026, 8, 15)])
assert double == aug

# A range across months, e.g. a notice period from 20 Sep to 19 Oct 2026.
print("20 Sep to 19 Oct 2026:", working_days(date(2026, 9, 20), date(2026, 10, 19),
                                             [date(2026, 10, 2)]))

August 2026 has five Saturdays, which is the case that breaks shortcuts such as "every other Saturday": the 1st, 15th and 29th are working days, the 8th and 22nd are off. Independence Day falls on the third Saturday, a working Saturday under this rule, so it does reduce the count to 23. The second check confirms that listing a holiday that is already a day off changes nothing. The cross-month range from 20 September to 19 October 2026 gives 22 working days, with Gandhi Jayanti on Friday 2 October as the one holiday. Enter the same three ranges into your workbook; if Excel does not agree, the holiday table is incomplete.

Cases to settle with HR

The last row is the real limit of the function: it can only remove days. If a Sunday is declared a working day for stock-taking, count it separately and add it to the result.

Keep one calendar, not many formulas

Once the off-days table exists, every report that counts working days should point at it: attendance, service-level ageing, capacity planning. When HR adds a holiday mid-year, one row changes and every count follows. Record who maintains the table and the date it was last updated in the sheet header.

This calendar feeds directly into calculate loss-of-pay days from an attendance sheet, and the same idea of an expected-days base underpins timesheet utilisation. Date arithmetic that matches how Indian offices actually work is part of the Excel foundation in our Data Analyst programme.