## WEEKDAYS between two dates

*Article last updated on March 25, 2018*

The NETWORKDAYS function allows you to count weekdays or workdays between two dates. It ignores weekends and a custom date list (optional) in the count, as well.

Formula in cell C5:

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

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

The DATEDIF function in cell E3 allows you to calculate days between two dates. Related articles Use MEDIAN function to […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

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

How to use the NETWORKDAYS function

The NETWORKDAYS function returns the number of working days between two dates, excluding weekends. It also allows you to ignore […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

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

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

Populate cells dynamically in a weekly schedule

In this post I am going to add one more function to a weekly schedule, an array formula allows you […]

I have created a monthly calendar template for you to download. Download excel calendar template Week starts with sunday Monthly […]

I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for each […]

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form