Search code examples
google-sheets

Counting agents on shift per hour and day of the week

I aim to tally the number of agents working during a specific hour and day using their regular schedules. However, my method doesn't accurately represent agents who work past midnight. For instance, if an agent works until 2 AM, they are incorrectly counted for the day they started rather than the following day.

Here is the link to the schedule file: https://docs.google.com/spreadsheets/d/1pan9psx_fIu5TD3RxKxO0l3I3usVjiwxHqWMRIwoCOE/edit?usp=sharing

To illustrate, the data displays the schedules of sales agents on the left side, showcasing their starting and ending times and the days they work each week.

Team schedule from Monday to Friday 5AM to 1PM

My goal is to determine the exact count of agents working on every day of the week. Based on the provided data (imagine a sample), here's a possible outcome:

Desired outcome

Tried a few formulas but neither of them worked as intended

=ARRAYFORMULA(
    SUM(
        IF(
            ($M2 >= $A$2:$A$100) * ($M2 < $B$2:$B$100),
            IF(
                OFFSET(D$2, 0, MATCH(N$1, D$1:J$1, 0)-1, COUNTA(D$2:D$100), 1) = "ON",
                1,
                0
            ),
            0
        )
    ) 
    + 
    SUM(
        IF(
            ($M2 < $B$2:$B$100) * ($A$2:$A$100 > $B$2:$B$100),
            IF(N$1="Monday",
                IF(J$2:J$100 = "ON", 1, 0), 
                IF(
                    OFFSET(D$2, 0, IF(MATCH(N$1, D$1:J$1, 0)-1=0, 6, MATCH(N$1, D$1:J$1, 0)-2), COUNTA(D$2:D$100), 1) = "ON",
                    1,
                    0
                )
            ),
            0
        )
    )
)

OR

=ARRAYFORMULA(
    SUM(
        IF(
            ($M2 >= $A$2:$A$100) * ($M2 < $B$2:$B$100) + 
            (($M2 < $B$2:$B$100) * ($A$2:$A$100 > $B$2:$B$100)),
            IF(
                OFFSET(D$2, 0, MATCH(N$1, D$1:J$1, 0)-1, COUNTA(D$2:D$100), 1) = "ON",
                1,
                0
            ),
            0
        )
    )
)

OR

=ARRAYFORMULA(SUM( ( (($M2 >= $A$2:$A$100) * ($M2 < $B$2:$B$100) + ($M2 >= $A$2:$A$100) * ($A$2:$A$100 > $B$2:$B$100) + ($M2 < $B$2:$B$100) * ($A$2:$A$100 > $B$2:$B$100)) * (OFFSET(D$1,0,MATCH(N$1, $D$1:$J$1, 0)-1, COUNTA(D$2:D$100), 1) = "ON") ), 1, 0))-1 " with "=ARRAYFORMULA(SUM( ( (($M2 >= $A$2:$A$100) * ($M2 < $B$2:$B$100) + ($M2 >= $A$2:$A$100) * ($A$2:$A$100 > $B$2:$B$100) + ($M2 < $B$2:$B$100) * ($A$2:$A$100 > $B$2:$B$100)) * (OFFSET(D$2,0,MATCH(N$1, D$1:J$1, 0)-1, COUNTA(D$2:D$100), 1) = "ON") ), 1, 0))-1
Solution

  • You have a table of shifts for staff(agents) showing their shift start and end time, and whether they are rostered to work on each of seven days (Mon-Sun).

    You want to calculate the number of agents who are working by day and by hour.

    This answer describes a method for generating that count. It involves a number of "helper" columns. Please note that it is almost certain that there are other ways to generate the same results.

    Agent Count: by hour by day

    count

    Shift table by agent: times and days

    • The duration of all shifts is 8 hours
    • The Shift start time will dictate whether a shift is:
      • completed within a single day, or
      • completed partially within a single day AND partially within the following day.
    • The Shift table indicates whether an agent is "rostered" for any given day:
      • "ON" = working
      • "OFF" = NOT working

    Comma separated hour values

    Shift hours can be used to develop strings of comma separated hour values. One can test if a shift hour can be found in the string and use the result in a count formula.

    For example:

    • Agent "Anthony" (Row#2) works a shift that starts at 5:00am and is completed before midnight.

      • The comma separated hour values for Agent Anthony can be described as: "5,6,7,8,9,10,11,12"
      • When counting agent hours for this shift for any given day, one need only use the standard string; subject to whether the agent is rostered "ON" for that day

    • Agent "Ceasar" (Row#72) works a shift that starts at 7:00pm; part of the shift is completed before midnight, and part of the shift is completed after midnight.

      • The comma separated hour values for Agent Caesar can be described as:
        "19,20,21,22,23" (pre-midnight) and "0,1,2" (post-midnight)
      • When counting agent hours for this shift for any given day, one must the standard pre-midnight string AND the "post-midnight" for the previous day; subject to whether the agent is rostered "ON" for the pre-midnight shift and/or rostered "ON" for the previous day's post-midnight shift.

    Helper Columns#1: Comma separated hour by shift
    Range: AE1:AO5

    The purpose of these helpers is to develop the comma separated strings that apply to each hour of the day (0-24)

    ae to ao

    • AF2 & AF3: start times for pre-midnight shifts
    • AG: Start time: cell AG2: =arrayformula(TEXT(V2:V25, "hh:mm")*24)
    • AH: End time: Cell AH2: =arrayformula(if(AG2:AG25>=16,((AG2:AG25)+8)-24,AG2:AG25+8))
    • AI: Test for "Shift: Whole-of-day":
      • Cell AI2: =MAP(AH2:AH25, LAMBDA(a,ARRAYFORMULA(isbetween(a,$AF$2,$AF$3,true,true))))
    • AJ: Test for "Shift: ends mid-night":
      • Cell AJ2: =arrayformula(if(AH2:AH25=0,true,false))
    • AK: "End Time: pre-midnight shifts":
      • Cell AK2: =arrayformula(if(AI2:AI25=true,AG2:AG25+8,if(AJ2=true,AG2:AG25+8,24)))
    • AL: "Comma separated hour values: Pre-midnight shifts"
      • Cell AL2: =MAP(AG2:AG25,AK2:AK25, LAMBDA(a,b,ARRAYFORMULA(join(",",SEQUENCE(1,b-a,a)))))
    • AM: "Start time: post-midnight shifts":
      • Cell AM2: =MAP(AI2:AI25,AJ2:AJ25, LAMBDA(a,b,ARRAYFORMULA(if(and(a=false, b=false), 0,))))
    • AN: "End time: post-midnight shifts":
      • Cell AN2: =MAP(AI2:AI25,AJ2:AJ25,AH2:AH25, LAMBDA(a,b,c,ARRAYFORMULA(if(and(a=false, b=false), c,))))
    • AO: "Comma separated hour values: Post midnight shifts":
      • Cell AO2: =MAP(AM2:AM25,AN2:AN25, LAMBDA(a,b,ARRAYFORMULA(iferror(join(",",SEQUENCE(1,b-a,a)),))))

    Helper Columns#2: Basic comma separated shift hour values by agent
    Range: K2:L100

    The purpose of these helpers is to identify the basic comma separated value for each agent shift.

    ktol

    • K: "Pre-midnight Shifts": Cell K2: =arrayformula(vlookup(A2:A100,$W$2:$AP$25,17,0))
    • L: "Post midnights shifts": Cell L2: =arrayformula(vlookup(A2:A100,$W$2:$AP$25,20,0))

    Helper Columns#3: Comma separated shift hours by agent by Day by roster="ON"
    Range: N1:T101

    The purpose of these helpers is to calculate the daily comma separated shift hours (including post midnight hors for the previous day) and subject to having been rostered "ON" on the given day and previous day.

    notot

    • N: Monday": Monday pre-midnight shifts (roster="ON") AND Sunday post-midnight shifts (roster="ON"):
      • Cell N2: =arrayformula({if(D2:D100="ON",$K$2:$K$100,)&","&if(J2:J100="ON",$L$2:$L$100,)})
    • O: Tuesday": Tuesday pre-midnight shifts (roster="ON") AND Monday post-midnight shifts (roster="ON"):
      • Cell O2: =arrayformula({if(E2:E100="ON",$K$2:$K$100,)&","&if(D2:D100="ON",$L$2:$L$100,)})
    • P: Wednesday": Wednesday pre-midnight shifts (roster="ON") AND Tuesday post-midnight shifts (roster="ON"):
      • Cell P2: =arrayformula({if(F2:F100="ON",$K$2:$K$100,)&","&if(E2:E100="ON",$L$2:$L$100,)})
    • Q: Thursday": Thursday pre-midnight shifts (roster="ON") AND Wednesday post-midnight shifts (roster="ON"):
      • Cell Q2: =arrayformula({if(G2:G100="ON",$K$2:$K$100,)&","&if(F2:F100="ON",$L$2:$L$100,)})
    • R: Friday": Friday pre-midnight shifts (roster="ON") AND Thursday post-midnight shifts (roster="ON"):
      • Cell R2: =arrayformula({if(H2:H100="ON",$K$2:$K$100,)&","&if(G2:G100="ON",$L$2:$L$100,)})
    • S: Saturday": Saturday pre-midnight shifts (roster="ON") AND Friday post-midnight shifts (roster="ON"):
      • Cell S2: =arrayformula({if(I2:I100="ON",$K$2:$K$100,)&","&if(H2:H100="ON",$L$2:$L$100,)})
    • T: Sunday": Sunday pre-midnight shifts (roster="ON") AND Saturday post-midnight shifts (roster="ON"):
      • Cell T2: =arrayformula({if(J2:J100="ON",$K$2:$K$100,)&","&if(I2:I100="ON",$L$2:$L$100,)})

    Agent Count Formula: by hour by day
    Range: V1:AC25

    Formula:

    • =iferror(query({N$2:N$100},"select count(Col1) where Col1 matches '.*\b"&$AG2&"\b.*' label count(Col1) '' ",1),)
    • Enter the formula in Cell W2
    • Copy the formula down to cell W25
    • select the range W2:W25 and copy the formula to X2:AC25

    =iferror(query({N$2:N$100},"select count(Col1) where Col1 matches '.\b"&$AG2&"\b.' label count(Col1) '' ",1),)

    Logic:

    • range: the agent comma separated shift hours for the given day. This range reflects shifts finishing before midnight as well as post-midnight shifts of the provious day (subject to the agent being rostered "ON" on those respective days).
    • where: a regex clause testing for a match with given shift hour. It is important to match the equivalent of a "whole word", otherwise the formula will return multiple values for the same shift. For example, if the shift starts at 5, thren the formula should ONLY return matches for "5", and should ignore "15".
    • label: to supress a label for the count
    • headers: suppressed.