Calculate days worked for a particular year between Start and End dates

[Momentman]

For example I have start and end dates that are not necessarily in the current year but I want to calculate how many days within each month in 2016 fall within the period. Can I get a formula that works.

the table should help clarify any questions
e.g

Start Date	End Date	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16
24-Jan-15	15-Dec-16	31	29	31	30	31	30	31	31	30	31	30	14
24-Aug-16	18-Dec-16	0	0	0	0	0	0	0	7	30	31	30	17
11-Jan-16	31-Aug-17	10	29	31	30	31	30	31	31	30	31	30	31

<tbody>
</tbody><colgroup><col><col><col span="7"><col><col span="4"></colgroup>

The first example runs from a previous year till mid dec-2016...all the days are captured except for dec 2016 that isn't a full month
For the second row, it starts in Aug-2016, so no days clocked till 24th of August and just 7 days in august and the others spread as expected.

Thanks for nay help I can receive

 

[Joe4]

Assuming that your grid starts in A1, so your first calculation goes in cell C1, use this formula:

=MAX(MIN($B2,DATE(YEAR(C$1),MONTH(C$1)+1,0))-MAX($A2,DATE(YEAR(C$1),MONTH(C$1),1))+1,0)

Note: I think I found a few problems with your example:
If the End Date is 12/15/2016, wouldn't that be 15 days, not 14?
And if 8/24/2016 is your start date, I get 8 days (8/24 - 8/31 is 8 days). And ending on 12/18 gives me 18 days.
And in your last row, if your Start Date is 1/11/2016, I get 21 days (1/11 - 1/31), not 11 days.

 

[Momentman]

Assuming that your grid starts in A1, so your first calculation goes in cell C1, use this formula:

=MAX(MIN($B2,DATE(YEAR(C$1),MONTH(C$1)+1,0))-MAX($A2,DATE(YEAR(C$1),MONTH(C$1),1))+1,0)

Note: I think I found a few problems with your example:
If the End Date is 12/15/2016, wouldn't that be 15 days, not 14?
And if 8/24/2016 is your start date, I get 8 days (8/24 - 8/31 is 8 days). And ending on 12/18 gives me 18 days.
And in your last row, if your Start Date is 1/11/2016, I get 21 days (1/11 - 1/31), not 11 days.

Your observations are very correct and your formula works like magic

Thanks

Does the expression EOMONTH(C$1,0) not equate to DATE(YEAR(C$1),MONTH(C$1)+1,0) for getting the last day of the month in which C$1 falls

 

[Joe4]

Does the expression EOMONTH(C$1,0) not equate to DATE(YEAR(C$1),MONTH(C$1)+1,0) for getting the last day of the month in which C$1 falls

That is true. I totally forgot about that function. You could incorporate that and simplify the formula a little.