Counting Days of Week in Date Ranges
 

In Col A I have a series of start dates and in Col B a series of end dates.

In cell D2 I am trying to calculate the number of Mondays in total across
the date ranges so something like
=INT((B3-WEEKDAY(B3+1-2)-A3+8)/7)+INT((B4-WEEKDAY(B4+1-2)-A4+8)/7)+INT((B5-WEEKDAY(B5+1-2)-A5+8)/7)

This formula works OK, as do others, where I actually have 3 date ranges but
sometimes there are more or less ranges so I need a formula (preferably not
an array formula) that doesn't return an error if say there are only dates in
1 or 2 rows. As this is part of a much bigger piece of work I can't change
the formula to suit the number of date ranges.

Any ideas would be appreciated.

Many thanks

Kewa

Counting Days of Week in Date Ranges
 

Use a column of formulas, one for each row, of the form

=IF(B3<"",INT((B3-WEEKDAY(B3+1-2)-A3+8)/7),"")

and then sum those....

HTH,
Bernie
MS Excel MVP


"nospaminlich" wrote in message
...
In Col A I have a series of start dates and in Col B a series of end dates.

In cell D2 I am trying to calculate the number of Mondays in total across
the date ranges so something like
=INT((B3-WEEKDAY(B3+1-2)-A3+8)/7)+INT((B4-WEEKDAY(B4+1-2)-A4+8)/7)+INT((B5-WEEKDAY(B5+1-2)-A5+8)/7)

This formula works OK, as do others, where I actually have 3 date ranges but
sometimes there are more or less ranges so I need a formula (preferably not
an array formula) that doesn't return an error if say there are only dates in
1 or 2 rows. As this is part of a much bigger piece of work I can't change
the formula to suit the number of date ranges.

Any ideas would be appreciated.

Many thanks

Kewa