Counting Days of Week in Date Ranges

G

Guest

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
 
B

Bernie Deitrick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top