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

 

[acwhelan]

Hello,

When I use this formula it returns a value of how many days are in the specific month as opposed to the count of days between the period. In the three cases below it should show 1 for April and 0's for the other months. Also, I am running 2007 so the EOMONTH is available.

Please advise!

Service Period Begin	Service Period End	Apr	May	Jun
03/08/2011	04/01/2011	30	31	30
03/01/2011	04/01/2011	30	31	30
02/04/2011	04/01/2011	30	31	30

<tbody>
</tbody>

 

[T. Valko]

Hello,

When I use this formula it returns a value of how many days are in the specific month as opposed to the count of days between the period. In the three cases below it should show 1 for April and 0's for the other months. Also, I am running 2007 so the EOMONTH is available.

Please advise!

Service Period Begin	Service Period End	Apr	May	Jun
03/08/2011	04/01/2011	30	31	30
03/01/2011	04/01/2011	30	31	30
02/04/2011	04/01/2011	30	31	30

<TBODY>
</TBODY>

Here are the results I get using the formula I suggested.

Book1
	A	B	C	D	E
1	Start	End	4/1/2011	5/1/2011	6/1/2011
2	3/8/2011	4/1/2011	1	0	0
3	3/1/2011	4/1/2011	1	0	0
4	2/4/2011	4/1/2011	1	0	0
Sheet1

 

[T. Valko]

Here are the results I get using the formula I suggested.

Sheet1

	A	B	C	D	E
1	Start	End	4/1/2011	5/1/2011	6/1/2011
2	3/8/2011	4/1/2011	1	0	0
3	3/1/2011	4/1/2011	1	0	0
4	2/4/2011	4/1/2011	1	0	0

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 61px"><COL style="WIDTH: 61px"><COL style="WIDTH: 61px"><COL style="WIDTH: 61px"><COL style="WIDTH: 61px"></COLGROUP><TBODY>
</TBODY>

Note that I use the 1st of the month dates for the column headers.

 

[acwhelan]

HI,

I don't know why it is not working. I have everything done you have suggested. Please note that my Period End falls in Column E and my Period Begin in Column D

Period Begin	Period End	4/1/2011	5/1/2011	6/1/2011
03/08/2011	04/01/2011	30	31	30
03/01/2011	04/01/2011	=MAX(0,MIN(EOMONTH(F$1,0),$E3)-MAX(F$1,$D3)+1)
02/04/2011	04/01/2011	30	31
02/03/2011	04/01/2011	30	31

<tbody>
</tbody>

 

[T. Valko]

HI,

I don't know why it is not working. I have everything done you have suggested. Please note that my Period End falls in Column E and my Period Begin in Column D

Period Begin	Period End	4/1/2011	5/1/2011	6/1/2011
03/08/2011	04/01/2011	30	31	30
03/01/2011	04/01/2011	=MAX(0,MIN(EOMONTH(F$1,0),$E3)-MAX(F$1,$D3)+1)
02/04/2011	04/01/2011	30	31
02/03/2011	04/01/2011	30	31

<TBODY>
</TBODY>

Here's a small sample file that demonstrates this.

zzzCountDays.xls 17kb

http://cjoint.com/?BHnqSHrIoPB

 

[acwhelan]

I got it! I had everything correct. What happened was the worksheet that I exported was not actually recognizing formatted dates as dates. By using the Text to columns feature and get the dates to 'actually' format as a date and was able to get the formula to calculate correctly.

This sent me for a whirlwind!

Thank you so much, I know I can always count on you guys!

 

[T. Valko]

I got it! I had everything correct. What happened was the worksheet that I exported was not actually recognizing formatted dates as dates. By using the Text to columns feature and get the dates to 'actually' format as a date and was able to get the formula to calculate correctly.

This sent me for a whirlwind!

Thank you so much, I know I can always count on you guys!

Excellent!

Thanks for the feedback!

 

[almiaris]

Hello,

i am trying to do the same but in the headers, I need to have only th month and not the complete First of the month with the year as well, since I have info into my Excel with various years. Is it possible to resolve this issue?

 

[razeryokes]

Hi Biff / T. Valko,
I subscribed to this forum only to thank you for your code. You just made my life much easier.
Thanks to acwhelan for documenting the question properly so others would like to answer.
Cheers,

 

[T. Valko]

You're welcome. Thanks for the feedback!