Distribute Hours Evenly Between 2 Dates but Only On Working Days

[RockFish74]

Hello,

I am trying to distribute predefined task hours evenly on working days (NETWORKDAYS) between 2 dates. I don't need to see the number of hours listed on each day, however, I do need to see the number of hours by month. Below is an example. I am looking for a formula to calculate the number of hours per month.

[TABLE="width: 410"]
<colgroup><col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2816;" span="2"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3214;"> <col width="77" style="width: 58pt; mso-width-source: userset; mso-width-alt: 2730;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2645;"> <tbody>[TR]
[TD="width: 72, bgcolor: transparent"]Start Date[/TD]
[TD="width: 79, bgcolor: transparent"]End Date[/TD]
[TD="width: 79, bgcolor: transparent"]Total Hours Required for Task[/TD]
[TD="width: 75, bgcolor: transparent"]# of Work Days[/TD]
[TD="width: 90, bgcolor: transparent"]Task Hours per Day[/TD]
[TD="width: 77, bgcolor: transparent"]Nov. 2018 Hours[/TD]
[TD="width: 74, bgcolor: transparent"]Dec. 2018 Hours[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]29-Nov[/TD]
[TD="bgcolor: transparent"]4-Dec[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]1.25[/TD]
[TD="bgcolor: transparent"]2.5[/TD]
[TD="bgcolor: transparent"]2.5[/TD]
[/TR]
</tbody>[/TABLE]

Thank you!

 

[OGCV1]

RockFish,

I have presume you would like to continue adding tasks so this nested IF might be what you are after

=IF(MONTH($A2)=MONTH($B2),$C2,IF(MONTH(F$1)=MONTH($A2),NETWORKDAYS($A2,EOMONTH($A2,0))*$E2,IF(MONTH(F$1)=MONTH($B2),$D2*$E2-E2,"")))

should work as long as a task does not last stretch into take place in three months. Let me know if I have missed the mark

OGCV

 

[RockFish74]

OGCV,

Thank you for responding. A task could extend for 3+ months i.e. allocated time is 40 hours and the task needs to be completed between 12/27 and 2/5. I will try using your formula a little later this morning. Thank you!