How to count a specific weekday between two dates in Excel

NETWORKDAYS.INTL with a weekday mask

You run a Monday yoga class from 2 November to 21 December and need to know how many sessions to book the hall for. Counting squares on a calendar works once. A formula works every term.

NETWORKDAYS.INTL was built to count working days with a custom weekend, and the third argument can be a text mask instead of a weekend code. The mask has seven characters for Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday. A 1 marks a day off and a 0 marks a day to count. "0111111" turns every day except Monday into a day off, so all that is left to count is Mondays.

Both the start and the end date are counted when they land on the right day. 2 November and 21 December are both Mondays, and both are in the 8. If "between" should leave them out, move the ends in: =NETWORKDAYS.INTL(B2+1,C2-1,"0111111") returns 6. NETWORKDAYS.INTL works in Excel 2010 and later.

Any other day, or two days at once

Move the 0 to the day you want. For the Saturday market stall from 7 November to 19 December, =NETWORKDAYS.INTL(B3,C3,"1111101") returns 7. For Sunday brunch shifts in November, "1111110" returns 5. Friday payroll dates in 2026 come out at 52 with "1111011".

Put a 0 on more than one day to count them together. The dog walker below goes out on Tuesdays and Thursdays, so the mask is "1010111", and 3 to 26 November gives 8 walks. With the rate in an input cell, the invoice line is one formula: =NETWORKDAYS.INTL(B4,C4,"1010111")*L2 returns 144 at the 18 typed in L2. Change L2 and the total follows.

Some years have 53 of one weekday. 2026 starts on a Thursday, so it has 53 Thursdays ("1110111") and 52 of every other day. That's the kind of detail that quietly breaks a "52 weeks x rate" budget.

Leave out holidays and closed days

The fourth argument is a list of dates to skip. Put the closed days in a column (F2:F4 here) and point the formula at it. Only closed days that land on a counted weekday make a difference: 26 November is a Thursday, so the dog walks drop from 8 to 7 and the invoice from 144 to 126. 21 December is a Monday, so the yoga count drops from 8 to 7. 25 December is a Friday, so the 2026 payroll count drops from 52 to 51.

Keep the list on its own sheet or in a named range if several formulas share it, and type real dates. A closed day typed as text is ignored without any warning.

Count a weekday in a month

For "how many Mondays are in this month", start at the first of the month and end at EOMONTH(date,0), the last day. With 1 November 2026 in H2, =NETWORKDAYS.INTL(H2,EOMONTH(H2,0),"0111111") returns 5. December 2026 has 4 Mondays and February 2027 has 4.

In Excel 365, one formula fills the whole column: =NETWORKDAYS.INTL(H2:H4,EOMONTH(H2:H4,0),"0111111") spills 5, 4 and 4. A month with five Mondays is exactly where a "four sessions a month" plan comes up short, so it's worth a column in any class, rota or billing sheet.

The SEQUENCE way, and a formula for older Excel

In Excel 365 or 2021 you can also list every date and test each one. SEQUENCE(C2-B2+1,1,B2) makes the dates from B2 to C2, WEEKDAY turns them into day numbers (Sunday 1, Monday 2, through Saturday 7) and SUMPRODUCT counts the matches. With the day number in J2, the yoga count is again 8. This version is easy to extend: ={3,5} in place of =J2 counts Tuesdays and Thursdays, and multiplying by ISNA(MATCH(SEQUENCE(C2-B2+1,1,B2),F2:F4,0)) drops closed days, giving 7.

Before Excel 2010 there was no NETWORKDAYS.INTL. The classic formula is =INT((WEEKDAY(B2-J2)+C2-B2)/7), with J2 using the same 1 = Sunday numbering. It also returns 8 here. It is hard to read, so leave a note next to it if someone else will inherit the file.