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

 

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

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