Days of week between dates
 

Column A has a start date and Column B has an end date. I then need the next
column to be the count of the number of Mondays between these dates, the next
to be the number of Tuesdays between these dates etc...

Thanks

Days of week between dates
 

Andy,

For a start date in A1 and End date in b1 put this in C1 and array enter
(see below)

=SUM(IF(WEEKDAY($A1-1+ROW(INDIRECT("1:"&TRUNC($B1-$A1)+1)))=COLUMN(A1),1,0))

Drag 6 columns right and you have the Sundays to Saturdays in the period
defined by the 2 dates. Drag dwn for additional rows.

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.



"Andy" wrote:

Column A has a start date and Column B has an end date. I then need the next
column to be the count of the number of Mondays between these dates, the next
to be the number of Tuesdays between these dates etc...

Thanks

Days of week between dates
 

I've gotten that to work (thankyou) but when testing realised an error in my
query. I actually want to count how many 'midnights' there are. i.e. if the
start and end date is 01/04/2009, I do not want a count of 1 against a
Wednesday (infact no count at all). If it is 01/04/2009 to 04/04/2009, I need
a Wed count of 1, a Thurs count of 1 and a Fri count of 1, but no Sat count.

Is this possible or getting horribly complicated!

Thanks in advance.

"Mike H" wrote:

Andy,

For a start date in A1 and End date in b1 put this in C1 and array enter
(see below)

=SUM(IF(WEEKDAY($A1-1+ROW(INDIRECT("1:"&TRUNC($B1-$A1)+1)))=COLUMN(A1),1,0))

Drag 6 columns right and you have the Sundays to Saturdays in the period
defined by the 2 dates. Drag dwn for additional rows.

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.



"Andy" wrote:

Column A has a start date and Column B has an end date. I then need the next
column to be the count of the number of Mondays between these dates, the next
to be the number of Tuesdays between these dates etc...

Thanks

Days of week between dates
 

=IF(A1=B1,"",SUMPRODUCT(--(WEEKDAY($A1-1+ROW(INDIRECT("1:"&INT($B1-$A1)+1)))=4)))

--
__________________________________
HTH

Bob

"Andy" wrote in message
...
I've gotten that to work (thankyou) but when testing realised an error in
my
query. I actually want to count how many 'midnights' there are. i.e. if
the
start and end date is 01/04/2009, I do not want a count of 1 against a
Wednesday (infact no count at all). If it is 01/04/2009 to 04/04/2009, I
need
a Wed count of 1, a Thurs count of 1 and a Fri count of 1, but no Sat
count.

Is this possible or getting horribly complicated!

Thanks in advance.

"Mike H" wrote:

Andy,

For a start date in A1 and End date in b1 put this in C1 and array enter
(see below)

=SUM(IF(WEEKDAY($A1-1+ROW(INDIRECT("1:"&TRUNC($B1-$A1)+1)))=COLUMN(A1),1,0))

Drag 6 columns right and you have the Sundays to Saturdays in the period
defined by the 2 dates. Drag dwn for additional rows.

This is an array formula which must be entered by pressing
CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly
brackets
'around the formula {}. You can't type these yourself. If you edit the
formula
'you must enter it again with CTRL+Shift+Enter.



"Andy" wrote:

Column A has a start date and Column B has an end date. I then need the
next
column to be the count of the number of Mondays between these dates,
the next
to be the number of Tuesdays between these dates etc...

Thanks

Days of week between dates
 

I think I have this sorted now. C2=IF(A2=B2,"Same Day",B2-1) and then
=IF($C2="Same
Day",0,SUM(IF(WEEKDAY($A2-1+ROW(INDIRECT("1:"&TRUNC($C2-$A2)+1)))=COLUMN(A2),1,0)))

"Bob Phillips" wrote:

=IF(A1=B1,"",SUMPRODUCT(--(WEEKDAY($A1-1+ROW(INDIRECT("1:"&INT($B1-$A1)+1)))=4)))

--
__________________________________
HTH

Bob

"Andy" wrote in message
...
I've gotten that to work (thankyou) but when testing realised an error in
my
query. I actually want to count how many 'midnights' there are. i.e. if
the
start and end date is 01/04/2009, I do not want a count of 1 against a
Wednesday (infact no count at all). If it is 01/04/2009 to 04/04/2009, I
need
a Wed count of 1, a Thurs count of 1 and a Fri count of 1, but no Sat
count.

Is this possible or getting horribly complicated!

Thanks in advance.

"Mike H" wrote:

Andy,

For a start date in A1 and End date in b1 put this in C1 and array enter
(see below)

=SUM(IF(WEEKDAY($A1-1+ROW(INDIRECT("1:"&TRUNC($B1-$A1)+1)))=COLUMN(A1),1,0))

Drag 6 columns right and you have the Sundays to Saturdays in the period
defined by the 2 dates. Drag dwn for additional rows.

This is an array formula which must be entered by pressing
CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly
brackets
'around the formula {}. You can't type these yourself. If you edit the
formula
'you must enter it again with CTRL+Shift+Enter.



"Andy" wrote:

Column A has a start date and Column B has an end date. I then need the
next
column to be the count of the number of Mondays between these dates,
the next
to be the number of Tuesdays between these dates etc...

Thanks