## Count specific weekdays in a date range

NETWORKDAYS function returns the number of whole workdays between two dates. The array formula in this blog post counts for example Mondays or any weekday in a date range. Later in this blog post I will show you how to exclude holidays.

**Array formula in cell D2:**

**How to create an array formula**

- Select cell D2
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula**

- Copy cell D2
- Select cell range E2:J2
- Paste

**Explaining formula in cell D2**

**Step 1 - Create dates in the date range**

$B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-(41000-1)))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-40999))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 41029-40999))-1)

becomes

41000+(ROW($A$1:INDEX($A$1:$A$1000, 30))-1)

becomes

41000+(ROW($A$1:$A$30)-1)

becomes

41000+({1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30}-1)

becomes

41000+{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29}

and returns

{41000, 41001, 41002, 41003, 41004, 41005, 41006, 41007, 41008, 41009, 41010, 41011, 41012, 41013, 41014, 41015, 41016, 41017, 41018, 41019, 41020, 41021, 41022, 41023, 41024, 41025, 41026, 41027, 41028, 41029}

**Step 2 - Convert dates to days of the week**

TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")

becomes

TEXT({41000, 41001, 41002, 41003, 41004, 41005, 41006, 41007, 41008, 41009, 41010, 41011, 41012, 41013, 41014, 41015, 41016, 41017, 41018, 41019, 41020, 41021, 41022, 41023, 41024, 41025, 41026, 41027, 41028, 41029}, "ddd")

and returns

{"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon"}

**Step 3 - Check if values in array are equal to the value in cell D1 (Mon)**

IF(TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")=D1, 1, 0)

becomes

IF({"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "Mon"}, "ddd")="Mon", 1, 0)

and returns

{0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1}

**Step 4 - Sum values in array**

SUMPRODUCT(IF(TEXT($B$1+(ROW($A$1:INDEX($A$1:$A$1000, $B$2-($B$1-1)))-1), "ddd")=D1, 1, 0))

becomes

SUMPRODUCT({0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 1})

and returns 5 in cell D2.

**Exclude holidays**

**Array formula in cell D7:**

**How to create an array formula**

- Select cell D7
- Paste array formula
- Press and hold Ctrl + Shift
- Press Enter

**How to copy array formula**

- Copy cell D7
- Select cell range E7:J7
- Paste

### Category: Count values

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Count unique distinct values in a column

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: First, let me explain […]Comments(26) Filed in category: Count unique distinct values, Count values, Excel

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

Count number of times a string exist in multiple cells using excel formula

Question: How do I find the number of occurances a word exists in a range of cells? It does not […]Comments(19) Filed in category: Count text values, Count values, Excel

Excel: List intervals between two values

jeyner asks: I need to count in a list the interval between the same value. Example list, 1-2-3-1-4-5-1-6-7-8-9-7-8-1 So the […]Comments(16) Filed in category: Count values, Excel, Range

### Category: Dates

Create a date range using excel formula

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/09-1/10/09 Cell B1 […]Comments(142) Filed in category: Dates, Excel

Lookup a value and find max date

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]Comments(83) Filed in category: Dates, Excel

Formula for matching a date within a date range in excel

Table of contents Match a date when a date range is entered in a single cell Match a date when […]Comments(48) Filed in category: Dates, Excel

Finding the nearest date in a range of dates using excel formula

Array formula in E3: =INDEX(A1:A10, MATCH(MIN(ABS(A1:A10-$E$1)), ABS(A1:A10-$E$1), 0)) Recommended articles: How to enter an array formula Select cell E3 Type […]Comments(42) Filed in category: Dates, Excel

Highlight overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]Comments(31) Filed in category: Excel, Overlapping

### 3 Responses to “Count specific weekdays in a date range”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Oscar, here's an alternative

=SUM(--(TEXT(ROW(INDIRECT("A"&$B$1&":A"&$B$2)),"ddd")=D1)) for the first formula

My alternative for the 2nd Formula (excluding the holidays)

=SUM(IF(NOT(ISNUMBER(MATCH(ROW(INDIRECT("A"&$B$1&":A"&$B$2)),Holidays,0))),(--(TEXT(ROW(INDIRECT("A"&$B$1&":A"&$B$2)),"ddd")=D1))))

chrisham,

Yes, thanks.

I seem to have answered this question already, here is also an alternative:

How many of a specific weekday falls between a start date and an end date except holidays