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

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

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

How to calculate the number of years between dates

The image above demonstrates the DATEDIF function calculating the number of complete years between two dates. Column B and column C […]

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

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