For anyone interested this script did what i wanted.
hour(time(HourStart, 'hh:mm')) as HourID,
time(HourStart, 'hh:mm') as HourStart,
time(HourEnd, 'hh:mm') as HourEnd,
num#(interval(time(rangemin([Vacated Time], HourEnd), 'hh:mm') - time(rangemax([Arrived Time], HourStart), 'hh:mm') , 'mm') ) as ActiveTime
floor([Arrived Time], 1/24) + (Iterno() - 1)/24 as HourStart,
floor([Arrived Time], 1/24) + (Iterno())/24 as HourEnd
floor([Arrived Time], 1/24) + Iterno()/24 <= ceil([Vacated Time], 1/24) + 0.02 //
This broke my time based data into hourly segments based on the start and end time. It calculates the time between each hour period so that i can see the profile of time being taken over the respective hours.
Many thanks to Oleg Troyansky for providing the answer in the previous post that i have adapted!
I actually have kind of the same problem.
I tried your solution and it works great. However, I also need to include the date aswell. I have have a list of shifts that have a start date with time and and end date with time. So I also need to know the day of the interval, not just the hour.
For example, if I have a shift that goes on for 2 days, I'm gonna have 2 slots for the same time stamp, lets say '03:00-04:00', without knowing which day is what.
Does anybody know a solution for this?