## 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

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

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

Formula for matching a date within a date range

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

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

How to calculate overlapping time ranges

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

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

Calculate last date of a given month

The formula in cell C3 calculates the last date for the given month and year in cell B3. =DATE(YEAR(B3), MONTH(B3)+1, […]

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

How to count workdays between two dates

Excel contains a great function that easily counts the number of workdays between two dates, it even allows you to […]

### 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.

**Contact Oscar**

You can contact me through this contact form

@@@ 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)