Identifying Number of Hours between a Time Range

Question

Hi All,

Just working on an assignment in which I have to identify how many hours fall within a certain time range.

For example: our time ranges are as follows

a1)  07:00 hrs to 12:00 hrs

b1) 18:00 hrs to 23:00 hrs

a person worked the following hrs:

- 09:00 hrs to 14:00 hrs; and

- 19:00 hrs to 21:00 hrs.

I want to say that 3 hrs worked fall under time range a1 and 5 hrs worked fall under time range b1.

I understand that this can be done through the formula tool, however it will be very appreciated if someone can suggest a more efficient approach.

Thanks

Saad

BenMoss · Accepted Answer

Hi,

I'd be interested to understand why you think the formula route is not efficient in this case, and to see how you have solved it using this (and then people won't replicate what you think is inefficient).

Another thing is I believe your result is incorrect, as the employee only works 2 hours between time slot b1 (or I have missed the objective).

Anyway, I personally wouldn't attach this using a formula tool, but instead a combination of tools.

Firstly I would use a formula tool to convert your times into datetime fields so that they are easier to work with.

I'd then use the generate rows tool to fill in the gaps, so instead of having one row with the start and end times, I'd have a single row for each hour between those time periods.

We can then merge and aggregate our data to get your desired result, as shown in the example attached.

Ben

OllieClarke · Accepted Answer

Hi @snasir here is how I would approach this problem, although I'd disagree with your expected outcome:

Using the generate rows tool we can fill in every hour between a min and max (starting from 1 hour after the min). Then we can join on the time to find out which hour falls into which range. If you had overlapping ranges then this would count each hour worked in both ranges, but I'd imagine that would be desired.

Hope that helps,

Ollie

Edit: @BenMoss got there first