Counting continuous hours used

[donnellya]

I'm looking to see how many times a provider didn't use a continuous 4 hour stretch of time. In the example below Andrew didn't had 8 available hours but didn't use the last 4 so he used 1 session. Bob had 8 hours too but never went 4 continuous hours without seeing a patient.

I used a cross tab tool to get the data in the format below. Data is on vertical axis by provider, date, hour, and patients seen. Thought this would be easier.

I was trying to use a multi row formula but I'm stuck. Any guidance? 

Provider	Date	8	9	10	11	12	13	14	15	Session
Andrew	1/19/2021	1	1	1	1	0	0	0	0	1
Bob	1/19/2021	1	1	1	0	1	1	1	0	2

[MarqueeCrew · Accepted solution]

@donnellya ,

I'm attaching a workflow that sorts your data (vertical) by PERSON, DATE and Sequential Hour.  It looks behind for the last 3 hours to see if they are all 0 (including current hour).  If so, it sets a value of 1 to a new 4hour flag.  It spans days to see if the string gets broken or continued.  Here's the multi-row formula that I used (setting Num Rows to 3):

IIF(
[Usage] = 0       AND [Row-1:Usage] = 0 AND 
[Row-2:Usage] = 0 AND [Row-3:Usage] = 0
,1,0)

Cheers,

Mark

[donnellya]

Thank you!

I made one update. I added a similar multi row formula after the other so I could count 4 hour blocks of time as 1. Image with example of why this is necessary below. 

Second multi row code:

IIF(
[Used In Person Slots] = 0
AND
[Row-1:4 hr flag] = 0
AND
[Row-1:Used In Person Slots] = 0
AND
[Row-2:4 hr flag] = 0
AND
[Row-2:Used In Person Slots] = 0
AND
[Row-3:4 hr flag] = 0
AND
[Row-3:Used In Person Slots] = 0
,1,0)