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

Edward..

I've written an addin with a function timeITcool.
it will work with larger intervals.. AND arrays.

see my dl page,
be sure to dl both the example and the addin.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Edward S) wrote:

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

keepITcool wrote in message . ..
Edward..

I've written an addin with a function timeITcool.
it will work with larger intervals.. AND arrays.

see my dl page,
be sure to dl both the example and the addin.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


(Edward S) wrote:

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

Hi Many thanks for your help with the "Timeitcool" function. It
worked really well. I truly appreciate your good work. Sorry I could
not respond earlier, as my Internet connection was down

Many Thanks

Edward