0

I have a group of employees and need to find a way to count the number of times an employee works greater than 5 days a row. Shifts are indicated by a different letter (H, P, Y, X) given the start time. I have attached a Google Sheet with the examples. Different combos of shifts do not matter, as long as over 5 days in a row are worked. I've tried some IF statements and some Arrays, but not very good at this coding thing. The actual Google Sheet will be much more expansive than this sample one, with the pay periods from last year, and will continue for the foreseeable future. Please help.

Below is a Google Sheets link for the example:

https://docs.google.com/spreadsheets/d/10Maov8GcWN7BgG4rR1eH8tuNIZqSr7isr38aPlFOVJQ/edit?usp=sharing

1
0

That can be done with string manipulation like this:

=arrayformula( 
  iferror( 
    rows( 
      query( 
        len( 
          transpose( 
            split( 
              regexreplace( 
                join( 
                  "", 
                  filter( 
                    regexreplace(trim(B3:B) & " ", "(?i)^([HPYX] )$", "_"), 
                    isnumber($A3:$A) 
                  ) 
                ), 
                "[^_]", 
                " " 
              ), 
              " ", false, true 
            ) 
          ) 
        ), 
        "where Col1 > 5", 0 
      ) 
    ) 
  ) 
)

See the new Solution sheet in your sample spreadsheet.

3
  • Thank you for your help!
    – Josh
    Aug 22 '21 at 23:05
  • I was trying to copy over the formula to the original spreadsheet and one thing that is causing some pain is that in the original, the H, P, Y, X shifts are also denoted by at least one following lowercase letter (i, s, r, tw). And the formula is not liking that @doubleunary
    – Josh
    Aug 24 '21 at 4:01
  • Consider sharing another publicly editable sample spreadsheet with realistic-looking data. Also show your hand-entered expected results there. Aug 24 '21 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.