Calculating Days In A Month Between 2 Date Ranges

[slinky]

Morning folks,

I've managed to sort the first half of this issue, in that I can calculate the number of days within a given month which fall inside specified to and from date parameters..

The formula I've used is thus :

=IF(EOMONTH(A13,0)<(EOMONTH($H$9,-1)+1),0,IF(EOMONTH($H$9,0)=EOMONTH(A13,0),EOMONTH(A13,0)-$H$9+1,IF(EOMONTH(A13,0)>EOMONTH($I$9,0),0,IF(EOMONTH(A13,0)>$I$9,$I$9-(EOMONTH($I$9,-1)),EOMONTH(A13,0)-(EOMONTH(A13,-1))))))

This examines a date value (A13 in this case) and assess whether any days within that month are between the two date parameters specified in H9 and I9.

H9 = 01/01/2011
I9 = 15/05/2011

Jan-11	31
Feb-11	28
Mar-11	31
Apr-11	30
May-11	15
Jun-11	0
Jul-11	0

<colgroup><col><col></colgroup><tbody>
</tbody>

What I need to do now is specify a second set of date parameters in H10 and I10 and add those into the assessment. The dates will NOT overlap.

So, for example, if I were to set H10 as 01/07/2011 and I10 as 15/07/2011, the results would appear as below

Jan-11 31 Feb-11 28 Mar-11 31 Apr-11 30 May-11 15 Jun-11 0 Jul-11 15 <colgroup><col><col></colgroup><tbody> </tbody>
Any thoughts? Excel 2k3 btw.. TIA

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[sgmpatnaik]

@slinky

Hi

Please download this file it may be clear your problem

https://dl.dropbox.com/u/75654703/Count Days1.xls

Thanks

Patnaik

 

[slinky]

Thanks for your suggestion. A user of another board posted this nice solution which works perfectly.

=MAX(0,MIN(EOMONTH(A13,0),$I$9)-MAX(DATE(YEAR(A13),MONTH(A13),1),$H$9)+1)+MAX(0,MIN(EOMONTH(A13,0),$I$10)-MAX(DATE(YEAR(A13),MONTH(A13),1),$H$10)+1)

 

[sgmpatnaik]

Glad you solved your problem

Thanks

Patnaik