# Thread: howmany wednesdays... (97)

1. ## howmany wednesdays... (97)

Hello all,

A friend of my has the following problem:
In cel A1 she puts a start date
In cel A2 she puts the end date
In cel A4 she wants to calculate how many wednesdays there where between those dates.....

Is it possible to create a formula that calculates this?

2. ## Re: howmany wednesdays... (97)

In the following layout cell A5 contains the answer
=SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(A2-A1)+1)))=A3,1,0))

This is an array formula and needs to be entered using Control+Shift+Enter

<table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center valign=bottom>1</td><td align=right valign=bottom>Thursday 01 January 2004</td><td valign=bottom>Start date</td><td align=center valign=bottom>2</td><td align=right valign=bottom>Saturday 01 May 2004</td><td valign=bottom>End Date</td><td align=center valign=bottom>3</td><td align=right valign=bottom>4</td><td valign=bottom>Day of week (1=Sunday, 2=Monday etc)</td><tr><td align=center valign=bottom>4</td><td align=right valign=bottom>

3. ## Re: howmany wednesdays... (97)

Great Tony,

Thanks for the quick answer.

4. ## Re: howmany wednesdays... (97)

Tony,

Is it possible you explane to my how this formula works?

5. ## Re: howmany wednesdays... (97)

=SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(A2-A1)+1)))=A3,1,0))

The formula can be broken down as follows.

("1:"&TRUNC(A2-A1)+1) - result for this example 1:122 - it is used to configure an array of all of the days between the first and last date, including first & last days there are 122 days

(A1-1+ROW(INDIRECT("1:"&TRUNC(A2-A1)+1))) - In an array formula this now produces in memory a list of all the days from first date to last date inclusive

Substituting the result of the first part of the equation, it gives
(A1-1+ROW(INDIRECT("1:122"))) - Excel now stores in it's memory 122 dates from 1st January 2004 to 1st May 2004

The WEEKDAY part now now changes the dates stored in memory to a number from 0 to 6, whereby 0=Sunday, 1=Monday etc (in this example the values stored in memory would now be 5,6,0,1,2,3,4,5,6,0,1... repeating this loop up to 122nd value

The IF formula then goes through this list and if the result is the same as the value in cell A3 it converts the number to 1, otherwise it converts it to 0 (the values stored in memory are now 0,0,0,0,0,0,1,0,0,0,0,0,0,1,0,0....)

Finally the SUM part adds all the values of this list, totalling 17.

Array formulas can be quite confusing if you have never used them before. Most of the calculations are done in memory and are not visible on the worksheet. Check Chip Pearson's Array Formulas for a detailed discussion on the topic.

6. ## Re: howmany wednesdays... (97)

Thanks again Tony,

I understand your formula and will have a look on that website to learn more about array formulas

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•