Counting Days in Specific Month between Two Dates

A

acwhelan

New Member
Joined
Nov 1, 2011
Messages
24
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 :)
 
Sort by date Sort by votes
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!
[TABLE="width: 400"]
<tbody>[TR]
[TD]Service Period Begin
[/TD]
[TD]Service Period End
[/TD]
[TD]Apr
[/TD]
[TD]May
[/TD]
[TD]Jun
[/TD]
[/TR]
[TR]
[TD]03/08/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]03/01/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]02/04/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]30
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
acwhelan said:
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!
[TABLE="width: 400"]
<TBODY>[TR]
[TD]Service Period Begin
[/TD]
[TD]Service Period End
[/TD]
[TD]Apr
[/TD]
[TD]May
[/TD]
[TD]Jun
[/TD]
[/TR]
[TR]
[TD]03/08/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]03/01/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]02/04/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]30
[/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...
Here are the results I get using the formula I suggested.

Book1
ABCDE
1StartEnd4/1/20115/1/20116/1/2011
23/8/20114/1/2011100
33/1/20114/1/2011100
42/4/20114/1/2011100
Sheet1
 
Upvote 0
T. Valko said:
Here are the results I get using the formula I suggested.

Sheet1

ABCDE

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]Start[/TD]
[TD="align: right"]End[/TD]
[TD="align: right"]4/1/2011[/TD]
[TD="align: right"]5/1/2011[/TD]
[TD="align: right"]6/1/2011[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]3/8/2011[/TD]
[TD="align: right"]4/1/2011[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]3/1/2011[/TD]
[TD="align: right"]4/1/2011[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]2/4/2011[/TD]
[TD="align: right"]4/1/2011[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</TBODY>
Click to expand...
Note that I use the 1st of the month dates for the column headers.
 
Upvote 0
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


[TABLE="width: 559"]
<tbody>[TR]
[TD]Period Begin
[/TD]
[TD] Period End
[/TD]
[TD]4/1/2011
[/TD]
[TD]5/1/2011
[/TD]
[TD]6/1/2011
[/TD]
[/TR]
[TR]
[TD]03/08/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]03/01/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD="colspan: 2"] =MAX(0,MIN(EOMONTH(F$1,0),$E3)-MAX(F$1,$D3)+1)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/04/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/03/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
acwhelan said:
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


[TABLE="width: 559"]
<TBODY>[TR]
[TD]Period Begin
[/TD]
[TD]Period End
[/TD]
[TD]4/1/2011
[/TD]
[TD]5/1/2011
[/TD]
[TD]6/1/2011
[/TD]
[/TR]
[TR]
[TD]03/08/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]03/01/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD="colspan: 2"]=MAX(0,MIN(EOMONTH(F$1,0),$E3)-MAX(F$1,$D3)+1)
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/04/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/03/2011
[/TD]
[TD]04/01/2011
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
Click to expand...
Here's a small sample file that demonstrates this.

zzzCountDays.xls 17kb

http://cjoint.com/?BHnqSHrIoPB
 
Upvote 0
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!
 
Upvote 0
acwhelan said:
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!
Click to expand...
Excellent!

Thanks for the feedback! :cool:
 
Upvote 0
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?
 
Upvote 0
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,
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Convert Date Problem
Replies
4
Views
743
Michele317
M
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
607
kevin9999
K
S
  • Question Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
334
bebo021999
B
I
  • Question Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
913
Insurance Dave
I
G
  • Solved
Counting zero billing days in Excel until current date
Replies
5
Views
929
awoohaw
awoohaw

Forum statistics

Threads
1,223,655
Messages
6,173,616
Members
452,525
Latest member
DPOLKADOT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top