CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Samuel
Samuel

Reputation: 21

Numbers of hours worked by hour of day

I am trying to determine how to count the number of hours worked per hour.

Basically, I have collected data from all shifts over a one-year period, and I am trying to understand how many hours they work per hour. At times, there are multiple employees out at once.

So during a year period, I would like to understand how many estimated hours per hour of the day they worked. 1-24 hours Monday - Sunday. See example sheet.

What is the best approach to take? I'm a little stuck.

See example here: https://docs.google.com/spreadsheets/d/1RnqOM4jKphqluvI2TrYnzCP4EvF4ATBvJbU10VcNs1k/edit?usp=sharing

Upvotes: 1

Views: 120

Answers (1)

tomf
tomf

Reputation: 535

This is kind of hacky, but see if it checks out

=ARRAYFORMULA(
  TRANSPOSE(
   QUERY(
    SPLIT(
     FLATTEN(
      IF(ISBLANK(C2:C),,
       DATE(2022,1,1)+WEEKDAY(B2:B)&"|"&
       IF(B2:B+TIME(SEQUENCE(1,24,0),0,0)>=B2:B+C2:C,
        IF(D2:D+TIME(SEQUENCE(1,24,0),0,0)<D2:D+E2:E,
         SEQUENCE(1,24,0),),))),
     "|",TRUE,TRUE),
    "select Col1, Count(Col1)
     where Col2 is not null
     group by Col1
     pivot Col2
     format Col1 'DDD'",0)))

Essentially, its checking if the date+time is greater than or equal to the start date + hours of the day and less than the end date + hours of the day. If its true, it'll return the start date with a pipe and that hour of the day. From there FLATTEN it and SPLIT by the pipe and run it through a QUERY.

The dates we're attaching are using the first Sunday of the year + the WEEKDAY value for the date so we can get clean text days in the proper order.

If this does work as expected and you'd like it broken down further, let me know. Change the IF(ISBLANK(C2:C) to IF(ISBLANK(C2:C5) to test it out on a smaller sample to verify the results.

Upvotes: 2

Related Questions