Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    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?

  2. #2
    Platinum Lounger
    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(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. #3
    2 Star Lounger
    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.

  4. #4
    2 Star Lounger
    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?

  5. #5
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    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. #6
    2 Star Lounger
    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

Posting Permissions

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