Forum Discussion
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...
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))
)
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.
Yes, too many games...
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.
That's interesting approach, especially if apply it to more adequate task. This one is not well defined initially, thus results are not very practical.
