## Count a specific weekday in a date range

*Article last updated on January 16, 2018*

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

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. What's on this […]

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 […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

Question: how can i get the date range as : 01/01/09 - 01/15/09, 01/16/09 - 01/31/09 and so on I […]

Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]

Populate cells dynamically in a weekly schedule

In this post I am going to add one more function to a weekly schedule, an array formula allows you […]

I have created a monthly calendar template for you to download. Download excel calendar template Week starts with sunday Monthly […]

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for each […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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