Decomposing number of days of an initial date and a final date according to the following month

Antoniozarzal

New Member
Joined
Apr 25, 2016
Messages
9
Hi MrExcel, I'm having a hard time trying to figure out how to do this.

I have a database where I register a date and a number of days lost per worker because an accident or an illness. For example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date of accident/illness[/TD]
[TD]Number of days lost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28/05/2016[/TD]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

That information goes to an accumulate of days lost per month, no issues there. As you can see, those 6 days will appear all of them as if all 6 days were in May. Now I need that the 4 days of may appear in May's statistics (sum of days) and the 2 left to appear in June's statistics (also a sum of days).

Is there a way to do this?

Thank you a lot guys for any response and excuse me if I've made a mistake with the language.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi,

The following sample does it:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date of acc./ill.<strike></strike>[/TD]
[TD]# Days<strike></strike>[/TD]
[TD]Jan 16[/TD]
[TD]Feb 16[/TD]
[TD]Mar 16[/TD]
[TD]Apr 16[/TD]
[TD]May 16[/TD]
[TD]Jun 16[/TD]
[TD]Jul 16[/TD]
[TD]Aug 16[/TD]
[TD]Sep 16[/TD]
[TD]Oct 16[/TD]
[TD]Nov 16[/TD]
[TD]Dec 16[/TD]
[TD]Jan 17[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]28/05/2016<strike></strike>[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]28/05/2016<strike></strike>[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]25/03/2016<strike></strike>[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]20/01/2016<strike></strike>[/TD]
[TD="align: right"]370[/TD]
[TD="align: right"]12[/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]
[TD="align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]

C1:O1 house real dates (01/01/2016, 01/02/2016....)
in C2:
Code:
=IF(C$1=DATE(YEAR($A2),MONTH($A2),1), MIN($B2,(EOMONTH(C$1,0)-$A2+1)),
IF(AND(C$1-DATE(YEAR($A2),MONTH($A2),1)>0,SUM($B2:B2)-$B2<$B2),MIN(($B2*2)-SUM($B2:B2),(EOMONTH(C$1,0)-C$1+1)),""))
copy right and down as necessary

See SAMPLE_SplitDaysAcrossMonths.xlsx if necessary

Regards
XLearner
 
Upvote 0
Hello Xlearner

Thank you a lot buddy, this is exactly what I was looking for!

I hope this can be useful for more people.

Greetings
Antonio.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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