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

04/24/2010	04/02/2011	2
03/02/2011	04/03/2011	3

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

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]

Start	End	Jan	Feb	Mar
1/2/2009	1/2/2009	1
1/16/2009	3/27/2009	16	28	27
2/3/2009	3/11/2009		26	11

<tbody>
</tbody>

 

[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.