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
    MVP
    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.

Resources