## How many of a specific weekday falls between a start date and an end date except holidays

*Article last updated on April 18, 2012*

**Steve asks:**

Right now I'm using the following formula to tell me how many of a specific defined day, ie Wednessdays,fall between a start date and stop date. Here is the formula:

The only thing is this doesn't take into account sepcific holidays that occur during the period that i dont want to include in the count. How can i adjust this so i can not include defined holidays.

**Answer:**

**Array formula in cell B12:**

**Array formula in cell B13:**

**How to create an array formula**

- Select cell B12
- Copy (Ctrl + c) above formula
- Paste (Ctrl + v) in formula bar
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**Explaining array formula in cell B12**

**Step 1 - Create dates**

ROW(INDIRECT(B4&":"&B5))

becomes

ROW(INDIRECT(40544&":"&40603))

becomes

ROW(40544:40603)

and returns

{40544; 40545; 40546; 40547; 40548; 40549; 40550; 40551; 40552; 40553; 40554; 40555; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}

*Step - 2 Check if date is a holiday*

MATCH(ROW(INDIRECT(B4&":"&B5)), $B$8:$B$9, 0)

becomes

MATCH({40544; 40545; 40546; 40547; 40548; 40549; 40550; 40551; 40552; 40553; 40554; 40555; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, $B$8:$B$9, 0)

becomes

MATCH({40544; 40545; 40546; 40547; 40548; 40549; 40550; 40551; 40552; 40553; 40554; 40555; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, {40548; 40555}, 0)

and returns

{#N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 2; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A}

**Step 3 - Filter non holiday dates**

IF(ISERROR(MATCH(ROW(INDIRECT(B4&":"&B5)), $B$8:$B$9, 0)), ROW(INDIRECT(B4&":"&B5)), "")

becomes

IF(ISERROR({#N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 2; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A}, ROW(INDIRECT(B4&":"&B5)), "")

becomes

IF(ISERROR({#N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 2; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A}, {40544; 40545; 40546; 40547; 40548; 40549; 40550; 40551; 40552; 40553; 40554; 40555; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, "")

and returns

{40544; 40545; 40546; 40547; ""; 40549; 40550; 40551; 40552; 40553; 40554; ""; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}

**Step 4 - Convert dates to weekdays**

MATCH(TEXT(IF(ISERROR(MATCH(ROW(INDIRECT(B4&":"&B5)), $B$8:$B$9, 0)), ROW(INDIRECT(B4&":"&B5)), ""), "dddd"), B6, 0)

becomes

MATCH(TEXT({40544; 40545; 40546; 40547; ""; 40549; 40550; 40551; 40552; 40553; 40554; ""; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, "dddd"), B6, 0)

becomes

MATCH(TEXT({40544; 40545; 40546; 40547; ""; 40549; 40550; 40551; 40552; 40553; 40554; ""; 40556; 40557; 40558; 40559; 40560; 40561; 40562; 40563; 40564; 40565; 40566; 40567; 40568; 40569; 40570; 40571; 40572; 40573; 40574; 40575; 40576; 40577; 40578; 40579; 40580; 40581; 40582; 40583; 40584; 40585; 40586; 40587; 40588; 40589; 40590; 40591; 40592; 40593; 40594; 40595; 40596; 40597; 40598; 40599; 40600; 40601; 40602; 40603}, "dddd"), "wednesday", 0)

becomes

MATCH({"saturday"; "sunday"; "monday"; "tuesday"; ""; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; ""; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"; "wednesday"; "thursday"; "friday"; "saturday"; "sunday"; "monday"; "tuesday"}, "wednesday", 0)

returns

{#N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A}

**Step 5 - Count weekdays**

=SUMPRODUCT(--(ISNUMBER(MATCH(TEXT(IF(ISERROR(MATCH(ROW(INDIRECT(B4&":"&B5)), $B$8:$B$9, 0)), ROW(INDIRECT(B4&":"&B5)), ""), "dddd"), B6, 0))))

becomes

=SUMPRODUCT(--(ISNUMBER({#N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A; 1; #N/A; #N/A; #N/A; #N/A; #N/A; #N/A})))

becomes

=SUMPRODUCT(--({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}))

becomes

=SUMPRODUCT(--({FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}))

becomes

=SUMPRODUCT({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0})

and returns 6 in cell B12.

**Download excel file**

How many of a specific weekday falls between a start date and an end date.xlsx

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

This user defined function creates an unique list of words and their frequency in selected range. User defined function: =FreqWords(cell_range, […]

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

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Highlight overlapping date ranges using conditional formatting

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

Identify overlapping date ranges

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

Highlight records based on overlapping date ranges and a condition

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

### 2 Responses to “How many of a specific weekday falls between a start date and an end date except holidays”

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

Hi Oscar... I'm trying to adapt your formula to determine in a date range of arrival dates, how many people I will have in a determine date. For example if I have 2 people arriving 05/14/17 and leaving 05/16/17 but also have 3 people arriving 05/15/17 leaving 05/17/18.

I need to know how many people I have in house for each day... 14 - 2 guests, 15-5 guests, etc.

Any ideas?

Thanks in advance!

Juan Gonzalez

I believe you are looking for this formula:

https://www.get-digital-help.com/2015/08/12/count-overlapping-days-across-multiple-date-ranges/