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.
Related articles
Most frequent value between two dates
In this article I will show you how to extract the most frequent value (text or number) between two dates [โฆ]
Question: Sam asks: Is there a formula that can count blocks For eg in your picture (see picture above) if [โฆ]
Count weekday within date range except holidays
Steve asks: Right now I'm using the following formula to tell me how many of a specific defined day, ie [โฆ]
La Thăng asks: I want to find day if given date and week, for example : if given Tuesday, 32th [โฆ]
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 [โฆ]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are [โฆ]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values [โฆ]
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 [โฆ]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download [โฆ]
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 [โฆ]
I will demonstrate three different techniques to build monthly date ranges in this article. Two of these techniques are easy because they [โฆ]
How to convert radians to fractions of pi
The formula in cell D3 converts the radian decimal value to fractions of pi. Formula in cell D3: =TEXT(C3/PI(),"?/?")&"*"&CHAR(182) Explaining [โฆ]
Populate cells dynamically in a weekly schedule
In this post I am going to add one more function to the weekly schedule I built in a previous [โฆ]
The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates [โฆ]
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
Paste image link to your comment.
English
Spanish
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