Distributing All Days Across Months by CPearson

[Edward S]

The formula below I have used in my worksheet is what I have copied
from Mr. Pearson's site http://www.cpearson.com/excel/distribdates.htm
(Many thanks to him and his great site), however it is based on the
Start and End date being 15-Jan-00 and 12-Jun-00 in Cel B17 and C17
respectively.

=MAX(0,(MIN($C17,DATE(YEAR(D$16),MONTH(D$16)+1,0))-MAX($B17,D$16)+1))

What would the formula be if I had several more intervals horizontally
as 09-Oct-00 and 23-Dec-00 in cell D17 and E17 and 12-Feb-01 and
06-Sep-01 in cell F17 and G17. Ofcourse the MONTHS (Jan-00,
Feb-00...Dec-00, Jan-01, Feb-01...Dec-01..) would then have to
probably start from H16 onwards. I am looking for a combined formula
considering all the intervals together. Currently I am adding the
above formula for each interval for example:

=MAX(0,(MIN($C17,DATE(YEAR(H$16),MONTH(H$16)+1,0))-MAX($B17,H$16)+1))
+ MAX(0,(MIN($E17,DATE(YEAR(H$16),MONTH(H$16)+1,0))-MAX($D17,H$16)+1))
+ MAX(0,(MIN($G17,DATE(YEAR(H$16),MONTH(H$16)+1,0))-MAX($F17,H$16)+1))

In my actual spreadsheet I have 8 such Date intervals, the formula
really gets too big. Is this the only way for me or is there a better
way

Any help would be greatly appreciated

Regards
Edward

 

[Kieran]

Edward,

Instead creating one massive formula, why not use a row on th
spreadsheet for each interval. Then the sum of the days for eac
interval can be simply extraxcted as a sum of the columns.

HI