Calculating Duration of Time Available

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.

Hourly Availability.csv

Date Time

Accepted answers

jarrod

@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.

Calculate Duration Of Time Available.yxmd

All comments

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

Consecutive Time.yxzp

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703