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
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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 17, 2024

m_tarler 

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 )
 )
)