Forum Discussion

pelserben's avatar
pelserben
Copper Contributor
Nov 04, 2024

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...
developer
excel
Macros and VBA
pelserben's avatar
pelserben
Copper Contributor
Nov 04, 2024
Sorry Peter, see my response to Hans. Thanks,
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 04, 2024

pelserben 

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
    )
))

 

Resources