Calculating Duration of Time Available

[eisesara]

Hi,

I have attached file. I need to figure out concurrent blocks of available time for associates. I have their chunks of "available time", but I need to show all potential concurrent chunks of time greater than 3 hours, so for example, for the second record in attached, it should show they are available from hours 11-14 ... a 4 hour block of time. I am stuck here. Any help is appreciated. Thanks.

[BenMoss]

Hi!

This is a great usecase for a multi-row formula tool; with this we can build a field which groups instances of consecutive records. 

We can then use the summerize tool to understand if the total number of records in a 'consecutive group' is greater than 3.

Example workflow attached.

Ben

[eisesara]

Thanks! This works to an extent, but if the start time and end time go over midnight, it doesn't work using min hour and max hour ... for example, the start time may be hour 23, and the end time may be hour 1 but it looks at 1 as the start time because it's the min hour.

Also need to show their duration between start time and end time, as well.

[jarrod · Accepted solution]

@eisesara when do you know the time when "over midnight"? all of these times are listed as a single day, unless you are saying for those records where we see 0,2,6,20,22,23 actually means Day-1 20, 22, 23 and CurrentDay 0,2,6? In that case, we could look at min/max for the day and when the total work day is over 12 hours, we assume the later hours are from the previous day and adjust any times after 12 by negative 1 day.

once that is completed you can sort, group, etc to get your 3 hours chunks.

[eisesara]

Thanks! I believe this did the trick!

[jarrod]

glad it helped 🙂