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
HansVogelaar's avatar
HansVogelaar
MVP
Aug 15, 2024

NicoleI210 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

m_tarler's avatar
m_tarler
Bronze Contributor
Aug 16, 2024

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.