## Forum Discussion

# Counting employees during each hour of the day

HansVogelaar Hans, can you help? so i started to try and tackle this (or at least what I think he wants/needs) in the attached file. My function first breaks up the text to create a grid of start times for each employee and a complimentary grid of end times. By making those times actual date-time I could address the overnight shifts. Then I created a grid of date-times for every hour in those corresponding days (actually shifted to the 1/2 hour). Finally I thought I could then MAP those date-times but summing up the instances they are both > start time grid and < end time grid. but my results of that MAP are not what I expect and I think it has to do with how the LAMBDA is handling the arrays. Hans, maybe you have an idea.

```
=LET(dates, B1:E1, times,B2:E20,
days,COLUMNS(dates),
timegrid, DROP(REDUCE("",SEQUENCE(days),LAMBDA(p,q,LET(
tt, CHOOSECOLS(times,q),
dd, INDEX(dates,q),
sText,TEXTBEFORE(tt,"-",,,,0),sTime,LEFT(sText,2)/24+RIGHT(sText,2)/60/24,
eText,TEXTAFTER(tt,"-",,,,0),etimeTemp,LEFT(eText,2)/24+RIGHT(eText,2)/60/24,etime,etimeTemp+((etimeTemp-sTime)<0),
HSTACK(p,sTime+dd,etime+dd)))),,1),
startgrid,CHOOSECOLS(timegrid,SEQUENCE(days,,,2)),
endgrid, CHOOSECOLS(timegrid,SEQUENCE(days,,2,2)),
TimesInDay, SEQUENCE(24,,0)/24/60,
timeDayGrid, TimesInDay+dates+0.5/24,
oneH, SEQUENCE(1,ROWS(endgrid),1,0),
oneV, SEQUENCE(COLUMNS(endgrid),1,1,0),
counts, MAP(timeDayGrid,LAMBDA(q,MMULT(MMULT(oneH,(q>startgrid)*(q<endgrid)),oneV))),
counts2, MAKEARRAY(ROWS(timeDayGrid), COLUMNS(timeDayGrid),LAMBDA(r,c, SUM((INDEX(timeDayGrid,r,c)>startgrid)*(INDEX(timeDayGrid,r,c)<endgrid)))),
out, VSTACK(dates,startgrid,endgrid),
testout, MMULT(MMULT(oneH,(45516.5>startgrid)*(45516.5<endgrid)),oneV),
counts2)
```

so in the above I'm relatively confident of the first 1/2 creating the startGrid and endGrid and then creating a timeDayGrid (simple a grid of times for each day). it is that last part I tried both MAP and MAKEARRAY and tried both SUM and MMULT inside. The last line 17-19 are just looking at test outputs.

No fresh ideas, just to play with what we have

```
=LET(
range, $A$1:$E$20,
data, DROP(range,1,1),
dates, TAKE( DROP(range,,1), 1),
HoursStart, SEQUENCE(24,,0,1/24),
HoursEnd, SEQUENCE(24,,1/24,1/24),
Hours, TEXT(HoursStart, "hh:mm") &
"-" &
TEXT(HoursEnd,"hh:mm"),
DateTime, LAMBDA(day,hhmm, day + REPLACE(hhmm, 3, , ":" ) ),
TimeStart, LAMBDA(day,times, DateTime(day, TEXTBEFORE(times, "-") ) ),
TimeEnd, LAMBDA(day,times, DateTime(day, TEXTAFTER(times, "-") ) ),
ShiftHours, LAMBDA(day,times, MOD( TimeEnd(day, times) - TimeStart(day, times), 1) ),
DayShiftStart, LAMBDA(day,times, TOCOL( TimeStart(day, times), 3 ) ),
DayShiftEnd, LAMBDA(day,times, TOCOL( TimeStart(day, times) + ShiftHours(day, times), 3 ) ),
ShiftRange, LAMBDA(fn,
DROP( REDUCE(
"",
SEQUENCE(COLUMNS(dates)),
LAMBDA(a,v,
LET(
col, CHOOSECOLS(range, v+1),
VSTACK(a, fn( TAKE(col,1), DROP(col,1) ) )
)
)
), 1)
),
ShiftStart, ShiftRange(DayShiftStart),
ShiftEnd, ShiftRange(DayShiftEnd),
CountEmployees, MAKEARRAY(
24, COLUMNS(dates),
LAMBDA(n,m,
SUM(
(INDEX(dates, 1, m) + INDEX( HoursStart, n, 1) <= ShiftEnd )*
(INDEX(dates, 1, m) + INDEX( HoursEnd, n, 1) > ShiftStart )
)
)
),
VSTACK(
HSTACK("Hour/Date", dates),
HSTACK( Hours, CountEmployees )
)
)
```