Forum Discussion
Counting employees during each hour of the day
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?
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.
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.
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 ) ) )m_tarler OK after all that, I went back and inserted the header date row and the time column on the left and discovered my problem, I did a sequence of 24 minuted instead of hours... ooof. I fixed that and it worked fine. My results are a little different than at least Sergei I think because I use the 1/2 hour segments so any time that starts or ends on the 1/2 hour it ignores for that hour, which I hope addresses the OP's original request.
m_tarler Sorry, I just came across this thread today... not too sure why I didn't get a notification when you tagged me the other day. Looks like you solved it already though! 🙂
It's interesting to see the different approaches taken by everyone thus far. I think I would be more inclined to unpivot the data into a separate range first, then work with the new structure. There's also a number of questions I would want answered:
- What does the "3" mean in the Sunday header "S3"? Is this the week number for the current pay period?
- Are there separate worksheets for each pay period? Or does one worksheet contain data for the entire year?
- What does "PTO 0", "PTO 8" and "PTO 10" mean? Are there any more variations of this?
- What does "LB" mean in the last entry on 8/11 ("1500-2300 LB")?
- Why do two of the entries on 8/15 begin with "r/"? What does this mean, and what comes after it?
- How many other similar "short codes" are there, what do they all mean, and how are they used? Can they be used as either a prefix or suffix to a scheduled time entry? Do any of them also contain a hyphen (e.g. "EDO-8")?
I agree with Patrick2788 that the current layout is not ideal. They appear to be tracking multiple pieces of information in a single cell, which is a common design flaw when starting with the desired output, then attempting to input data into the same structure. Data entry should be done in a separate table (or tables) with appropriate fields for each piece of information, so it can easily be summarized and output as desired (e.g. pivot tables, power query, dynamic array formulas, etc.).
That's just my two cents and is in no way meant to denigrate the solutions presented thus far. I quite enjoy seeing the different methods each person uses, as it's a great way to pick up new tips. Cheers!
