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

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,142
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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 DateEnd DateJan-16Feb-16Mar-16Apr-16May-16Jun-16Jul-16Aug-16Sep-16Oct-16Nov-16Dec-16
24-Jan-1515-Dec-16312931303130313130313014
24-Aug-1618-Dec-160000000730313017
11-Jan-1631-Aug-17102931303130313130313031

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top