Results 1 to 6 of 6
Thread: howmany wednesdays... (97)

20040115, 11:40 #1
 Join Date
 Apr 2002
 Location
 Amersfoort, Utrecht, Netherlands
 Posts
 116
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20040115, 12:10 #2
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: howmany wednesdays... (97)
In the following layout cell A5 contains the answer
=SUM(IF(WEEKDAY(A11+ROW(INDIRECT("1:"&TRUNC(A2A1)+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>

20040115, 12:10 #3
 Join Date
 Apr 2002
 Location
 Amersfoort, Utrecht, Netherlands
 Posts
 116
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: howmany wednesdays... (97)
Great Tony,
Thanks for the quick answer.

20040115, 13:38 #4
 Join Date
 Apr 2002
 Location
 Amersfoort, Utrecht, Netherlands
 Posts
 116
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: howmany wednesdays... (97)
Tony,
Is it possible you explane to my how this formula works?

20040115, 14:10 #5
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: howmany wednesdays... (97)
=SUM(IF(WEEKDAY(A11+ROW(INDIRECT("1:"&TRUNC(A2A1)+1)))=A3,1,0))
The formula can be broken down as follows.
("1:"&TRUNC(A2A1)+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
(A11+ROW(INDIRECT("1:"&TRUNC(A2A1)+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
(A11+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.

20040115, 14:12 #6
 Join Date
 Apr 2002
 Location
 Amersfoort, Utrecht, Netherlands
 Posts
 116
 Thanks
 0
 Thanked 0 Times in 0 Posts
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