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.
- PeterBartholomew1Aug 16, 2024Silver Contributor
I was on grandchild-minding duty (times 2), picnicking and walking around parkland. I will take a look after the "Financial Modelling with Modern Excel" bootcamp meeting, unless all is resolved by the time I make it!
- m_tarlerAug 16, 2024Bronze Contributor
HansVogelaar as long as we are throwing out a life line, maybe we can call on a few other active contributors SergeiBaklan , Patrick2788 , @JKPieterse , @djclements . can anyone help me understand why my MAP or MAKEARRAY functions don't produce the counts for each time slot in my LAMBDA above? thank you.
- Patrick2788Aug 16, 2024Silver Contributor
There's two things that are holding me back for indulging in this adventure:
1. The data arrangement is not ideal. Why are the start/end times in the same cell?
2. What is the desired goal/return from the formula?
I suspect this could be made a lot simpler by arranging the data vertically in 4 columns: Employee, Start, End, Duration. Much easier to analyze and a Gantt (Stacked column) chart could be created.