Hello,
Based on this previous thread: Sum longest streak
I have this data set, very similar problem, but with a change in orientation:
This data goes from D2 (Total Shifts in a Row) to L3 (5.38)
I'm using this in Column D3 to help calculate the max consecutive shifts, which is working perfect.
=IF(arrayformula( max( len( split( join( "", left(F3:AC3 > 0) ), "F" ) ) ) )=24,0,arrayformula( max( len( split( join( "", left(F3:AC3 > 0) ), "F" ) ) ) ))
What I'm looking to get is IF the employee has worked 5 or more shifts (Column D3), then calculate the SUM of the consecutive 5 or 6 days hours worked.
In this case this employee worked 5 consecutive days between 11/14 and 11/18 and the formula on column E3 should provide a total of hours worked of 36.67.
I can't figure this out! I'm using Google Sheets.
Based on this previous thread: Sum longest streak
I have this data set, very similar problem, but with a change in orientation:
| Total Shifts In Row | Consecutive Hours | 11/14 | 11/15 | 11/16 | 11/17 | 11/18 | 11/19 | 11/20 |
| 5 | 5.43 | 8.47 | 8.30 | 6.83 | 7.63 | 5.38 |
I'm using this in Column D3 to help calculate the max consecutive shifts, which is working perfect.
=IF(arrayformula( max( len( split( join( "", left(F3:AC3 > 0) ), "F" ) ) ) )=24,0,arrayformula( max( len( split( join( "", left(F3:AC3 > 0) ), "F" ) ) ) ))
What I'm looking to get is IF the employee has worked 5 or more shifts (Column D3), then calculate the SUM of the consecutive 5 or 6 days hours worked.
In this case this employee worked 5 consecutive days between 11/14 and 11/18 and the formula on column E3 should provide a total of hours worked of 36.67.
I can't figure this out! I'm using Google Sheets.
