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.
Get Excel *.xlsx file
WEEKDAYS between two dates.xlsx
Dates basic formulas 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 […]
Excel categories
9 Responses to “Count specific WEEKDAYS between two dates”
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.
@@@ 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
Hey, Oscar.
Thank you for sharing this formula. This could be very useful to me but, unfortunately, I was not able to make the full formula work as it shows in the opening image. Would it be possible to provide an Excel *.xlsx file with the full formula?
How to count number of dates between two dates?
Like, if I want to know how many times 29 February is between two dates?
Or, how many times 11 June is between two dates?
Thus, dates disregarding year)
Dan,
Formula in cell E3:
=SUMPRODUCT((DATE(1900, MONTH(B2:B11), DAY(B2:B11))=DATE(1900, MONTH(E2), DAY(E2)))*1)