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

Momentman

Well-known Member
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

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Assuming that your grid starts in A1, so your first calculation goes in cell C1, use this formula:
Code:
``=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.

Last edited:
Assuming that your grid starts in A1, so your first calculation goes in cell C1, use this formula:
Code:
``=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.

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

Last edited:
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.

Replies
2
Views
167
Replies
3
Views
434
Replies
1
Views
372
Replies
4
Views
502
Replies
10
Views
274

1,196,306
Messages
6,014,563
Members
441,828
Latest member
cofracr

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.

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

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