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

[TABLE="width: 160"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Jan-11
[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Feb-11[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Mar-11
[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Apr-11[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]May-11
[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Jun-11
[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Jul-11
[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 160"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 160"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Jan-11[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Feb-11[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD]Mar-11[/TD]
[TD="align: right"]31[/TD]
[/TR]
[TR]
[TD]Apr-11[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]May-11[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Jun-11[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Jul-11[/TD]
[TD="align: right"]15
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Any thoughts?
Excel 2k3 btw..

TIA
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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