Forum Discussion

NicoleI210's avatar
NicoleI210
Copper Contributor
Aug 15, 2024

Counting employees during each hour of the day

Hello. I am having a hard time figuring out how to use functions to count the number of staff I have working during each hour of the day. I am a beginner, but I enlisted the help of someone who has m...
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Aug 18, 2024

NicoleI210 

I started by trying to understand the nature of the problem rather that attempting to evaluate the proposed solution in isolation.  I may have made a bit of a meal of it but conclusions such as 'it is easier to stack the weekly hours as a continuous datetime column probably hold.  I have used thunks to hold the counts for employee before aggregating but I could have nested blocks of formula instead.

=LET(
    timeLine, EXPAND(TOCOL(dates + SEQUENCE(24, , 0) / 24, TRUE), , 2, 0),
    empHrsϑ, BYROW(
        times,
        LAMBDA(emplTimes,
            LET(
                startHour, REGEXEXTRACT(emplTimes, "^\d{2}"),
                startMins, REGEXEXTRACT(emplTimes, "\d{2}(?=-)"),
                departHour, REGEXEXTRACT(emplTimes, "\d{2}(?=..$)"),
                departMins, REGEXEXTRACT(emplTimes, "\d{2}$"),
                startTime, dates + (startHour + startMins / 60) / 24,
                departTime, dates + (departHour + departMins / 60) / 24,
                finishTime, departTime + (departTime < startTime),
                emplStarts, SORT(VSTACK(timeLine, EXPAND(TOCOL(startTime, 3), , 2, 1))),
                emplFinish, SORT(VSTACK(timeLine, EXPAND(TOCOL(finishTime, 3), , 2, 1))),
                emplHours, 24 * TAKE(emplFinish - emplStarts, , 1),
                filter, VSTACK(DROP(emplFinish - emplStarts, 1, 1) <> -1, TRUE),
                TOROW(THUNK(WRAPCOLS(FILTER(emplHours, filter), 24, TRUE)))
            )
        )
    ),
    REDUCE(EXPAND(0, 24, 1, 0), empHrsϑ, LAMBDA(acc, hrsϑ, IFERROR(acc + hrsϑ(), acc)))
)

 

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 19, 2024

PeterBartholomew1 , it looks like count for the last hour in last date is not correct.

Played a bit with regex as well

=LET(
    time, SEQUENCE(24, , 0) / 24,
    datetime, dates + time,
    timeStart, REGEXREPLACE(
        times,
        "(^\d{2})(\d{2})(.*)",
        "$1:$2"
    ) * 1 + dates,
    timeEndRaw, REGEXREPLACE(
        times,
        "(.{5})(\d{2})(\d{2})",
        "$2:$3"
    ) * 1 + dates,
    timeEnd, timeEndRaw + (timeStart > timeEndRaw),
    shiftStart, TOCOL(timeStart, 3),
    shiftEnd, TOCOL(timeEnd, 3),
    count, MAP(
        datetime,
        LAMBDA(v, SUM((v >= shiftStart) * (v <= shiftEnd)))
    ),
    VSTACK(HSTACK("Time/Date", dates), HSTACK(time, count))
)
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Aug 19, 2024

    SergeiBaklan 

    Yes, I should have checked the result more carefully.  I was trying to understand the structure of the problem and pretty much stopped when I got numbers!  I then side-tracked myself with playing REGEX games and working towards Lambda functions as modules, for example

    Durationλ
= LAMBDA(shift,
    LET(
      ExtractTimeλ, LAMBDA(s, SUM({60,1}/60/24 * REGEXEXTRACT(s, "\d{2}", 1))),
      valid?,       REGEXTEST(shift, "\d{4}-\d{4}"),
      start,        REGEXEXTRACT(shift, "^\d{4}"),
      end,          REGEXEXTRACT(shift, "\d{4}$"),
      startTime,    MAP(start, ExtractTimeλ),
      endTime,      MAP(end,   ExtractTimeλ),
      IF(valid?, endTime - startTime + (startTime>endTime), "")
    )
  );

    Too many games to play and too little time.

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Aug 22, 2024

        SergeiBaklan 

        I have taken another crack at the problem.  My goal was to reduce the solution to the same form as I use for banded tax problems, sales commission tiers, FIFO calculations and a number of other problems that involve overlapping range calculation.  Namely, I stack all types of events in a single block and sort them into a single table for calculation.  Nowadays GROUPBY and PIVOTBY can be valuable.

         

        =LET(
    timeLine,       EXPAND(TOCOL(dates + SEQUENCE(24, , 0) / 24, , TRUE), , 2, 0),
    validTimes,     REGEXEXTRACT(times, "\d{4}-\d{4}"),
    assocDates,     TOCOL(IF(ISERROR(validTimes), NA(), dates), 3, TRUE),
    clockTimes,     TOCOL(validTimes, 3, TRUE),
    startTimes,     MAP(
        TEXTBEFORE(clockTimes, "-"),
        LAMBDA(t, SUM({60, 1} * REGEXEXTRACT(t, "\d{2}", 1) / 60 / 24))
    ),
    endTimes,       MAP(
        TEXTAFTER(clockTimes, "-"),
        LAMBDA(t, SUM({60, 1} * REGEXEXTRACT(t, "\d{2}", 1) / 60 / 24))
    ),
    finishTimes,     endTimes + (startTimes > endTimes),
    startDatetimes,  EXPAND(assocDates + startTimes, , 2, 1),
    finishDatetimes, EXPAND(assocDates + finishTimes, , 2, -1),
    keyDatetimes,    VSTACK(timeLine, startDatetimes, finishDatetimes),
    events,          SORT(keyDatetimes),
    sortedDatetimes, TAKE(events, , 1),
    headcount,       SCAN(0, TAKE(events, , -1), SUM),
    timeslot,        FLOOR.MATH(sortedDatetimes, 1 / 24),
    duration,        DROP(sortedDatetimes, 1) - sortedDatetimes,
    manhours,        headcount * duration,
    day,             INT(timeslot),
    hour,            MOD(timeslot, 1),
    DROP(PIVOTBY(hour, day, 24 * manhours, SUM, , 0, , 0), , -1)
)

         

        There might be a case for modularising the formula further to separate the restructuring (using REGEX) from the subsequent calculation.  The calculations for headcount and time durations could also be Lambda function.  I feel I should use more in the way of functional breakdown of problems using a tree of Lambda functions than I do.  The "1500-0330" is still a bit of an unresolved problem since it pushes 3½ hours labour outside the period of the analysis.

Resources