Counting Days in Specific Month between Two Dates

[acwhelan]

Hello,

I am trying to determine a formula that will calculate the the number of days that fall within a certain month.

Example: April
[TABLE="width: 435"]
<tbody>[TR]
[TD="align: left"]04/24/2010[/TD]
[TD]04/02/2011[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: left"]03/02/2011[/TD]
[TD]04/03/2011[/TD]
[TD]3[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]


I need to do this for the months of April to July so I can calculate a proration based on the days falling in a specific month.

Please assist

 

[Trouttrap2]

this will calculate the number of days in a month. Where A1= your date
=DAY(EOMONTH(A1,0))

 

[acwhelan]

How will this calculate the the months in April without using both dates?

 

[Robert Mika]

How did you arrive at 3 for first line?
6 days for 2010 and 2 days for 2011 gives 8

 

[acwhelan]

[TABLE="width: 240"]
<tbody>[TR]
[TD="class: xl63, width: 64, bgcolor: transparent, align: left"]Start
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: left"]End
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: left"]Jan
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: left"]Feb
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent, align: left"]Mar
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]1/2/2009
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]1/2/2009
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]1/16/2009
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3/27/2009
[/TD]
[TD="bgcolor: transparent, align: right"]16
[/TD]
[TD="bgcolor: transparent, align: right"]28
[/TD]
[TD="bgcolor: transparent, align: right"]27
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]2/3/2009
[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]3/11/2009
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]26
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[/TR]
</tbody>[/TABLE]

 

[acwhelan]

So I am trying to find the number of days that fall in each particular month between two dates

 

[acwhelan]

I found this formula: =MAX(0,MIN($E2,F$1+31-DAY(F$1+31))-MAX($D2,F$1)+1)

TO someone with a similar question where
E2: End date (April 1)
D2: Start Date (April 2)
F1: Name of Month : APR

However, this formula just return the total number of days in April; hence, 30. When in fact it shoudl return 2.

 

[acwhelan]

??

 

[acwhelan]

Hello,

I was wondering if you found a formula that would caluculate this? Please let me know if I need to clarify. I realize at first the dates I provided were confusing as it should have said April 2011.

I believe the second chart shows the numbers I am looking to received.

Please help!

Thanks!

 

[T. Valko]

Hello,

I was wondering if you found a formula that would caluculate this? Please let me know if I need to clarify. I realize at first the dates I provided were confusing as it should have said April 2011.

I believe the second chart shows the numbers I am looking to received.

Please help!

Thanks!

Try this...

Book1
	A	B	C	D	E
1	Start	End	1/1/2009	2/1/2009	3/1/2009
2	1/2/2009	1/2/2009	1	0	0
3	1/16/2009	3/27/2009	16	28	27
4	2/3/2009	3/11/2009	0	26	11
Sheet1

This formula entered in C2:

=MAX(0,MIN(EOMONTH(C$1,0),$B2)-MAX(C$1,$A2)+1)

Copy across as needed then down as needed.

Note that the EOMONTH function requires the Analysis ToolPak
add-in be installed if you're using a version of Excel prior to
Excel 2007. If you enter the formula and get a #NAME?
error look in Excel help for the EOMONTH function. It'll tell you
how to fix the problem.