Days of week between dates

[Andy]

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

 

[Mike H]

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]

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.

 

[Bob Phillips]

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

 

[Andy]

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)))