Forum Discussion
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 more experience and they aren't able to figure it out either.
I have 45 employees in a 24/7 department, so there are night shift staff that start on 1 day and finish on the next - that is throwing a huge wrench into things.
The schedule also has 30 minute lunches built into their day, so there is a 30 minute overlap that I don't want to include those people into.
I do my schedule in excel per pay period as shown below. I am willing to adapt the way I currently do the schedule if needed, I just need to save myself time and prevent miscounting errors.
Any help would be appreciated.
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.
I started by trying to understand the nature of the problem rather that attempting to evaluate the proposed solution in isolation. I may have made a bit of a meal of it but conclusions such as 'it is easier to stack the weekly hours as a continuous datetime column probably hold. I have used thunks to hold the counts for employee before aggregating but I could have nested blocks of formula instead.
=LET( timeLine, EXPAND(TOCOL(dates + SEQUENCE(24, , 0) / 24, TRUE), , 2, 0), empHrsϑ, BYROW( times, LAMBDA(emplTimes, LET( startHour, REGEXEXTRACT(emplTimes, "^\d{2}"), startMins, REGEXEXTRACT(emplTimes, "\d{2}(?=-)"), departHour, REGEXEXTRACT(emplTimes, "\d{2}(?=..$)"), departMins, REGEXEXTRACT(emplTimes, "\d{2}$"), startTime, dates + (startHour + startMins / 60) / 24, departTime, dates + (departHour + departMins / 60) / 24, finishTime, departTime + (departTime < startTime), emplStarts, SORT(VSTACK(timeLine, EXPAND(TOCOL(startTime, 3), , 2, 1))), emplFinish, SORT(VSTACK(timeLine, EXPAND(TOCOL(finishTime, 3), , 2, 1))), emplHours, 24 * TAKE(emplFinish - emplStarts, , 1), filter, VSTACK(DROP(emplFinish - emplStarts, 1, 1) <> -1, TRUE), TOROW(THUNK(WRAPCOLS(FILTER(emplHours, filter), 24, TRUE))) ) ) ), REDUCE(EXPAND(0, 24, 1, 0), empHrsϑ, LAMBDA(acc, hrsϑ, IFERROR(acc + hrsϑ(), acc))) )PeterBartholomew1 , it looks like count for the last hour in last date is not correct.
Played a bit with regex as well
=LET( time, SEQUENCE(24, , 0) / 24, datetime, dates + time, timeStart, REGEXREPLACE( times, "(^\d{2})(\d{2})(.*)", "$1:$2" ) * 1 + dates, timeEndRaw, REGEXREPLACE( times, "(.{5})(\d{2})(\d{2})", "$2:$3" ) * 1 + dates, timeEnd, timeEndRaw + (timeStart > timeEndRaw), shiftStart, TOCOL(timeStart, 3), shiftEnd, TOCOL(timeEnd, 3), count, MAP( datetime, LAMBDA(v, SUM((v >= shiftStart) * (v <= shiftEnd))) ), VSTACK(HSTACK("Time/Date", dates), HSTACK(time, count)) )Yes, I should have checked the result more carefully. I was trying to understand the structure of the problem and pretty much stopped when I got numbers! I then side-tracked myself with playing REGEX games and working towards Lambda functions as modules, for example
Durationλ = LAMBDA(shift, LET( ExtractTimeλ, LAMBDA(s, SUM({60,1}/60/24 * REGEXEXTRACT(s, "\d{2}", 1))), valid?, REGEXTEST(shift, "\d{4}-\d{4}"), start, REGEXEXTRACT(shift, "^\d{4}"), end, REGEXEXTRACT(shift, "\d{4}$"), startTime, MAP(start, ExtractTimeλ), endTime, MAP(end, ExtractTimeλ), IF(valid?, endTime - startTime + (startTime>endTime), "") ) );Too many games to play and too little time.
