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.
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.
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!
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!
- And I agree with you and Patrick on all your points, but unfortunately the OP has gone silent. My pings were really to understand where I went wrong and thought it was an issue with how I was using the LAMBDA and hitting myselfself when I realized my mistake was minutes instead of hours because those minutes cost me many hours (lol).
As for SergeiBaklan latest post I just weep as you flex those new functions I still don't have access to. LOL. just kidding (sort of) but would totally want the OP to chime in on some of those points to figure out which issues need to be addressed in the formula vs the data entry. Either way, I always appreciate seeing your solutions and always learn something new.
