Days Per Week Between 2 Dates

Out of all the formulas (formulae to the more educated than me) that I work with, I would say that Date formulas are the hardest to get right. Maybe not so surprising considering how weeks don't divide nicely into months. It really is very naughty of them.

Anyway, I put together a formula to work how many days exist in each week between 2 dates. For it to work as expected, the following must be true -

The end date must be equal or higher than the start date (D'oh!)

The start date must be equal to or higher than the first week's date

The end date must be equal to or lower than the last week's date + 6



Weeks that are not within the date range show as blank. Note the formula also includes the start date as being a day, so if we consider the total days, the calculation would be

End Date - Start Date + 1 Day

And the formula? With the Start Date and the End Date being in Cells A2 and B2 respectively, select the appropiate cells starting from Cell C2, enter this formula, then push Ctrl + Enter simultaneously. (Alternatively, enter the formula into Cell C2 and drag to the right)

=IF(AND(C1>=$A2-6,C1<=$B2),IF(AND($A2-C1>0,$B2<C1+7),$B2-$A2+1,IF(OR($B2=C1,$A2=$B2),1,IF(AND($A2-C1>0,$A2-C1<7),C1+7-$A2,IF(AND($B2-C1>0,$B2-C1<7),$B2-C1+1,7)))),"")

Can you shorten it? Great if you can, don't hurt your head though :-)

There is room for improvement though. As is, holidays are not calculated, neither weekends or public holidays. If you come up with a way, (perhaps and another formula or 2 in rows below?), let me know and I will append it to the bottom of this post!

Update
Sébastien Labonne of The Financial Modeler has come up with a much shorter formula! (About a third the length of mine and far fewer cell references!)

=MIN(MAX(0,C1+7-$A2),7)-MIN(MAX(0,C1+6-$B2),7)

Well done Sébastien! :-)