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:

Start Date

End Date

Hours

Jan

Feb

Mar

Apr

May

June

July

Aug

Sep

Oct

Nov

Dec

15-Apr-14

5-May-14

80

64

16

4-Mar-14

6-Jun-14

150

43

48

48

11

1-Jul-14

30-Sep-14

100

35

32

33

<tbody>
</tbody>

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
1	Start Date	End Date	Hours	1/1/14	1/2/14	1/3/14	1/4/14	1/5/14	1/6/14	1/7/14	1/8/14	1/9/14	1/10/14	1/11/14	1/12/14
2	15-Apr-14	5-May-14	80	0	0	0	64	16	0	0	0	0	0	0	0
3	4-Mar-14	6-Jun-14	150	0	0	43	48	48	11	0	0	0	0	0	0
4	1-Jul-14	30-Sep-14	100	0	0	0	0	0	0	35	32	33	0	0	0

<tbody>
</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