howmany wednesdays… (97)

Topic

New Reply

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?

Reply | Quote

Replies

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

	A	B
1	Thursday 01 January 2004	Start date
2	Saturday 01 May 2004	End Date
3	4	Day of week (1=Sunday, 2=Monday etc)
4

Reply | Quote

Great Tony,

Thanks for the quick answer.

Reply | Quote

Great Tony,

Thanks for the quick answer.

Reply | Quote

Tony,

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

Reply | Quote

=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.

Reply | Quote

Thanks again Tony,

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

Reply | Quote

Thanks again Tony,

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

Reply | Quote

=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.

Reply | Quote

Tony,

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

Reply | Quote

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

	A	B
1	Thursday 01 January 2004	Start date
2	Saturday 01 May 2004	End Date
3	4	Day of week (1=Sunday, 2=Monday etc)
4

Reply | Quote