Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Identifying Number of Hours between a Time Range

snasir
5 - Atom

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

 

 

 

3 REPLIES 3
BenMoss
ACE Emeritus
ACE Emeritus

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
15 - Aurora
15 - Aurora

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

OllieClarke_0-1617704920421.png

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

snasir
5 - Atom

Thanks Ben, really liked your approach. Since still fairly new to Alteryx, I thought there maybe a formula or tool to address this. Sorry my error, correct hours under 1b should only be two hrs (probably I was loosing it towards the end of the day).  Cheers 

Labels