Days of week between dates

  • Thread starter Thread starter Andy
  • Start date Start date
A

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
 
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.
 
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.
 
=IF(A1=B1,"",SUMPRODUCT(--(WEEKDAY($A1-1+ROW(INDIRECT("1:"&INT($B1-$A1)+1)))=4)))
 
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)))
 
Back
Top