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

[Antoniozarzal]

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:

Date of accident/illness	Number of days lost
28/05/2016	6

<tbody>
</tbody>

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.

 

[XLearner]

Hi,

The following sample does it:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date of acc./ill.<strike></strike>	# Days<strike></strike>	Jan 16	Feb 16	Mar 16	Apr 16	May 16	Jun 16	Jul 16	Aug 16	Sep 16	Oct 16	Nov 16	Dec 16	Jan 17
2	28/05/2016<strike></strike>	6					4	2
3	28/05/2016<strike></strike>	4					4
4	25/03/2016<strike></strike>	80			7	30	31	12
5	20/01/2016<strike></strike>	370	12	29	31	30	31	30	31	31	30	31	30	31	23

<tbody>
</tbody>

C1:O1 house real dates (01/01/2016, 01/02/2016....)
in C2:

=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

 

[Antoniozarzal]

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.

 

[XLearner]

You're welcome & Thanks for posting back