Distribute Project hours over the given period

[Abhay Gadiya]

I have a sample data with Start Date, End date and Project hours. I need to distribute these to various months in manner similar to below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Hours[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]15-Apr-14[/TD]
[TD]5-May-14[/TD]
[TD]80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]64[/TD]
[TD]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4-Mar-14[/TD]
[TD]6-Jun-14[/TD]
[TD]150[/TD]
[TD][/TD]
[TD][/TD]
[TD]43[/TD]
[TD]48[/TD]
[TD]48[/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1-Jul-14[/TD]
[TD]30-Sep-14[/TD]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]35[/TD]
[TD]32[/TD]
[TD]33[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Since, i do not have permission to attach files, I can't upload the sample formula which I am using. It seems to be very lengthy.

Can someone help me build up this formula

 

[StephenCrump]

This formula in D2, copied down and across, matches your results:

=MAX(0,NETWORKDAYS(MAX(D$1,$A2),MIN(EDATE(D$1,1)-1,$B2)))/NETWORKDAYS($A2,$B2)*$C2

Is this what you wanted?

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
Start Date	End Date	Hours

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1/1/14[/TD]
[TD="align: right"]1/2/14[/TD]
[TD="align: right"]1/3/14[/TD]
[TD="align: right"]1/4/14[/TD]
[TD="align: right"]1/5/14[/TD]
[TD="align: right"]1/6/14[/TD]
[TD="align: right"]1/7/14[/TD]
[TD="align: right"]1/8/14[/TD]
[TD="align: right"]1/9/14[/TD]
[TD="align: right"]1/10/14[/TD]
[TD="align: right"]1/11/14[/TD]
[TD="align: right"]1/12/14[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]15-Apr-14[/TD]
[TD="align: right"]5-May-14[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]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: center"]3[/TD]
[TD="align: right"]4-Mar-14[/TD]
[TD="align: right"]6-Jun-14[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]11[/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: center"]4[/TD]
[TD="align: right"]1-Jul-14[/TD]
[TD="align: right"]30-Sep-14[/TD]
[TD="align: right"]100[/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"]35[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet1
​

 

[Abhay Gadiya]

Thanks seems to be working.

I made little change as follows for formula in D2

=MAX(0,NETWORKDAYS(MAX(D$1,$A2),MIN(EOMONTH(D$1,0),$B2)))/NETWORKDAYS($A2,$B2)*$C2