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 activity. I want to divide these manhours among the specified months for each activity so that I can create a Bell Curve for manhours for each month of the year. 

 

While this can be accomplished using the Excel Macros Developer program, I am unsure how to perform the extraction and separation correctly. Could someone please help me with this process? 

 

Thank you!

 

PS: The full version has over 2,000 activities

 

StartFinishManhours
22 February 202617 May 20261871
11 March 202623 May 2026250
17 December 202625 February 20275988
23 May 202608 August 20264790
08 August 202625 August 2026250
25 August 202630 September 20261248
30 September 202610 October 2026524
10 October 202614 November 20262994
21 July 202630 September 20265988
developer
excel
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 04, 2024

    pelserben 

    How should the hours be allocated to the months? Based on the number of calendar days in each month? Or on the number of working (non-weekend) days in each month? Do we have to take public holidays into account?

    • pelserben's avatar
      pelserben
      Copper Contributor
      Nov 04, 2024

      HansVogelaar Hans, the schedule is based on 6 days a week, only Fridays are excluded, as this project is in the Middle East. The process is to extract the months-to-month between the start and finished dates and then allocate the manhours prorate to each month based on the months within each line activity and the respective days in the relevant months. I was informed that one must use Developer with Macros in Excel, and then insert a formula and the system will automatically do the extraction and manhour allocation. But I don't have the knowledge to do the formula/s.  

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Nov 04, 2024

    pelserben 

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