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

 

[websmile]

Just registered as well to say THANK YOU Biff! Made my day, even if i have no idea what the formula really does in it's singular steps....but will reach there one day I loved the way it was visualized, result and formula, just had to adjust it to my needs, GREAT!

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Charles2017]

Try this...

Sheet1

*	A	B	C	D	E

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 67px;"><col style="width: 67px;"><col style="width: 60px;"><col style="width: 60px;"><col style="width: 60px;"></colgroup><tbody>
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD="align: right"]Start[/TD]
[TD="align: right"]End[/TD]
[TD="align: right"]1/1/2009[/TD]
[TD="align: right"]2/1/2009[/TD]
[TD="align: right"]3/1/2009[/TD]

[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD="align: right"]1/2/2009[/TD]
[TD="align: right"]1/2/2009[/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"]1/16/2009[/TD]
[TD="align: right"]3/27/2009[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]27[/TD]

[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD="align: right"]2/3/2009[/TD]
[TD="align: right"]3/11/2009[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]11[/TD]

</tbody>

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.

This is almost exactly what I needed. How would you count the number of workdays in a month using the same layout?

 

[Eric W]

Welcome to the forum.

Try this formula in C2:

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

If you care about holidays, you should use the NETWORKDAYS.INTL function which allows you to add a list of holidays.

 

[Charles2017]

Welcome to the forum.

Try this formula in C2:

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

If you care about holidays, you should use the NETWORKDAYS.INTL function which allows you to add a list of holidays.

Thank You!
That worked perfectly! Thank you so much!. Maybe this post could be pinned as it is very helpful.

 

[Eric W]

Glad it worked for you!

The trouble with pinning useful threads is that there are so many of them here! A better way to search might be nice, but that would still be no guarantee of finding what you want. Worst case scenario, open a new thread and someone will spot it and respond to it. If you add to an old thread, it might not be seen.

 

[sunny3131986]

Welcome to the forum.

Try this formula in C2:

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

If you care about holidays, you should use the NETWORKDAYS.INTL function which allows you to add a list of holidays.

Thank you so much.. You are a genius i was searching for this formula for months for our budget report and had to manually calculate.

 

[Eric W]

I'm glad you found this useful!

Feel free to start a new thread if you can't find what you're looking for.