Count specific WEEKDAYS between two dates
If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula.
Formula in cell C8:
Explaining formula in cell C8
The INDEX function allows you to create a cell range with a specific size. We want it to be the same size as there are days between the start date and end date + 1.
A1:INDEX(A1:A10000,C3-B3+1))
becomes
A1:INDEX(A1:A10000,43121-43101+1))
becomes
A1:INDEX(A1:A10000,21)) and returns A1:A21.
The row function then returns the row number for each cell.
ROW(A1:INDEX(A1:A10000,C3-B3+1))
becomes
ROW(A1:A21) and returns this array {1; 2; 3; ... ; 21}
The array must start with 0 (zero) so we subtract the array with 1.
WEEKDAY(B3+ROW(A1:INDEX(A1:A10000,C3-B3+1))-1
becomes
{1; 2; 3; ... ; 21} - 1 and returns {0; 1; 2; ... ; 20}
It is now time to add the start date to the array.
B3+ROW(A1:INDEX(A1:A10000,C3-B3+1
becomes
43101+{0; 1; 2; ... ; 20} and returns {43101; 43102; ... ;43121}
The WEEKDAYS function converts the dates to numbers of the week. Sunday is 1 and Saturday is 7.
WEEKDAY(B3+ROW(A1:INDEX(A1:A10000,C3-B3+1))-1)
becomes
WEEKDAY({43101; 43102; ... ;43121}) and returns {2; 3; ... ; 1}
We need to compare the values in the array with the weekday numbers we want to count. To count Mondays and Wednesdays we need number 2 and 4.
WEEKDAY(B3+ROW(A1:INDEX(A1:A10000,C3-B3+1))-1)=B6:C6
becomes
{2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1}={2,4} and returns the following array containing boolean values:
The SUMPRODUCT function then sums the numbers but first we need to convert the boolean values to numbers. It is easy, simply multiply with 1.
SUMPRODUCT((WEEKDAY(B3+ROW(A1:INDEX(A1:A10000,C3-B3+1))-1)=B6:C6)*1)
becomes
SUMPRODUCT({1,0; 0,0; 0,1; 0,0; 0,0; 0,0; 0,0; 1,0; 0,0; 0,1; 0,0; 0,0; 0,0; 0,0; 1,0; 0,0; 0,1; 0,0; 0,0; 0,0; 0,0}) and returns 6 in cell C8.
Download Excel *.xlsx file
WEEKDAYS between two dates.xlsx
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 […]
Count dates inside a date range
How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]
Use MEDIAN function to calculate overlapping ranges
I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it may be dates, […]
7 days (weekly) date ranges using a formula
Shannon asks:I need a formula that if I enter a start date in field B1 such as 6/8/11 it will […]
Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]
The image above shows a formula in cell D3 that extracts the latest date in cell range B3:B15. =MAX(B3:B15) The MAX […]
The DATEDIF function in cell E3 allows you to calculate days between two dates. Related articles Use MEDIAN function to […]
6 Responses to “Count specific WEEKDAYS between two dates”
Leave a Reply to Michael (Micky) Avidan
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.
@@@ To whom it may concern,
To my opinion there are at least two (shorter) formulas to achieve he task.
See Picture:
Michael (Micky) Avidan
I wrote the article and I sure would like to know the shorter formulas.
Wordpress seems to have eaten your image link. Can you post the formulas?
Thank you for commenting.
/Oscar
Hi, Oscar,
Your wish is my command
=SUM(INT((C3-MOD(C3-{2,4},7)-B3+7)/7))
=NETWORKDAYS.INTL(B3,C3,"0101111")
By the way - should the link to the picture be within Apostrophes - or not ?
Hereunder I will try to add in both ways:
_________________________
Micky
Michael (Micky) Avidan
By the way - should the link to the picture be within Apostrophes - or not ?
It looks like wordpress won't allow the img tags anymore, perhaps it never has.
Thank you for the formulas, the NETWORKDAYS.INTL function is great for this task.
/Oscar
Hi, Oscar,
It could be wise to remove the paragraph: "How to add a picture to your comment".
It would be much easier to upload the picture to any file host site and paste the link to it (without any HTML tags).
Like That: https://postimg.cc/jw1YDM4n
Micky
Michael (Micky) Avidan
You are absolutely right, I have changed the instructions below the articles.
It will take a few days before all website cache is purged and refreshed.
Thanks
/Oscar