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
[TABLE="class: grid, width: 910"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD="align: right"]Jan-16[/TD]
[TD="align: right"]Feb-16[/TD]
[TD="align: right"]Mar-16[/TD]
[TD="align: right"]Apr-16[/TD]
[TD="align: right"]May-16[/TD]
[TD="align: right"]Jun-16[/TD]
[TD="align: right"]Jul-16[/TD]
[TD="align: right"]Aug-16[/TD]
[TD="align: right"]Sep-16[/TD]
[TD="align: right"]Oct-16[/TD]
[TD="align: right"]Nov-16[/TD]
[TD="align: right"]Dec-16[/TD]
[/TR]
[TR]
[TD="align: right"]24-Jan-15[/TD]
[TD="align: right"]15-Dec-16[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]24-Aug-16[/TD]
[TD="align: right"]18-Dec-16[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]11-Jan-16[/TD]
[TD="align: right"]31-Aug-17[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[/TR]
</tbody><colgroup><col><col><col span="7"><col><col span="4"></colgroup>[/TABLE]


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.