Forum Discussion
pelserben
Nov 04, 2024Copper Contributor
Allocating manhours to a month-to-month from a construction schedule
Good day, I need assistance with separating manhours by month from a construction schedule. The schedule includes a start date, a finish date, and the total man-hours for the duration of each activ...
PeterBartholomew1
Nov 04, 2024Silver Contributor
This is a 365 formula that works on a 7 day week basis.
Worksheet formula
= REDUCE(0,Activity,DistributeHoursλ(Manhours, Start, Finish))
DistributeHoursλ
= LAMBDA(_hours,_start,_finish, LAMBDA(acc, seq,
LET(
hours, INDEX(_hours, seq),
start, INDEX(_start, seq),
finish, INDEX(_finish, seq),
timeline, 1 + EOMONTH(DATE(2026, 1, 0), SEQUENCE(16, , 0)),
open, SORT(VSTACK(start, timeline)),
close, SORT(VSTACK(finish, timeline)),
days, DROP(close - open, 1),
acc + hours * days / SUM(days)
)
))
pelserben
Nov 04, 2024Copper Contributor
Sorry Peter, see my response to Hans. Thanks,
- PeterBartholomew1Nov 04, 2024Silver Contributor
Don't worry, the problem has a sufficiently generic interest to make it worth solving as a challenge. I have introduced NETWORKDAYS but I could easily have made an error accounting for the various inclusive or exclusive date intervals. Using NETWORKDAYS with weekend = 16
DistributeWkHrsλ = LAMBDA(_hours,_start,_finish, LAMBDA(acc, seq, LET( hours, INDEX(_hours, seq), start, INDEX(_start, seq), finish, 1 + INDEX(_finish, seq), timeline, 1 + EOMONTH(DATE(2026, 1, 0), SEQUENCE(16, , 0)), open, SORT(VSTACK(start, timeline)), close, SORT(VSTACK(finish, timeline)), days, DROP( NETWORKDAYS.INTL(open, close, 16) - NETWORKDAYS.INTL(close, close, 16), 1), hrs, hours * days / SUM(days), acc + hrs ) ))