Count a specific weekday in a date range
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate in this article counts, for example, Mondays or any weekday in a date range. Later in this article, I will show you how to exclude holidays.
Update! I recommend using the NETWORKDAYS.INTL function to count weekdays, it is a lot smaller and easier to work with.
Regular formula in cell D2:
Copy cell D2 and paste to cell range E2:J2. You need to change "1000000" to "0100000" in cell E2, in cell F2 change it to "0010000" and so on.
The text string "1000000" allows you to specify which days are weekdays and which are weekends. There are seven characters and they can be 1 or 0 (zero), 1 indicates it is to be counted and 0 not to be counted.
However, in this case, I am using the string to exclude specific days, I am not using it to define which days are weekends. Read more about the function here: How to use the NETWORKDAYS.INTL function
Array formula in cell D2 (old formula):
How to create an array formula
- Select cell D2.
- Paste array formula.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter.
- Release all keys.
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
Update! You can use the NETWORKDAYS.INTL function to count weekdays and ignore holidays as well.
Formula in cell D7:
Array formula in cell D7 (old formula):
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 values category
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Dates category
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 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
This article demonstrates formulas that show if a date range is overlapping another date range. The second section shows how […]
This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]
This article explains how to find the smallest and largest value using two conditions. In this case they are date […]
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]
Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]
The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
Table of Contents Filter unique distinct values based on a date range Filter unique distinct values based on a date […]
Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]
This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
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
Paste image link to your comment.
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