Splitting duration between time frames

[JRe5777]

Hello everybody,

I would really appreciate your help on this subject.

I have a table of bus trips, with 2 columns for start of route and end of route, so we can calculate how many minutes/hours the trip takes.

Then I have 3 timeframes (I grouped by start hour):

00:00 - 05:59

06:00 - 20:59

21:00 - 23:59

Imagine one of the trips starts at 20:30 and ends at 21:30.

I want to have a way of saying that 30 min were in the time frame "06:00 - 20:59" and the other 30 min on the time frame "21:00 - 23:59".

Do you have any tip on how to do that?

I've tried some hourly calculation, with the limit of the time frame and the start or end date, but then the hour is a number and the date is a date and it doesn't calculate.

Thanks in advance!

[v-piga-msft]

Hi @JRe5777 ,

I still a little confused about your scenario.

If it is convenient, could you share your data sample as table format and your desired output so that I could understand your logic better.

Best  Regards,

Cherry

[JRe5777]

Hello @v-piga-msft 

Thank you for your reply, here goes the example. Let me know if you understand it.

I want to know how much of the trip is in one timeframe and how much of the trip is in another one.

Bus Nº	Trip	Departure	Time Fram Departure	Arrival	Time frame arrival	Kms	Trip Duration	Duration in hours
12	1	18/01/2017 20:45	06:00 - 20:59	18/01/2017 21:03	21:00 - 23:59	2.4	00:18:00	0.30
12	2	18/01/2017 20:51	06:00 - 20:59	18/01/2017 20:52	06:00 - 20:59	2.4	00:19:00	0.02
		Desired Visual Result (Matrix) in minutes of duration
		Trip	06:00 - 20:59	21:00 - 23:59
		1	15	3
		2	19	0

[v-piga-msft]

Hi @JRe5777 ,

Sorry I still have confused about your desired output.

If it is convenient, could you explain the logic of your output. Why the value is 15 duting 06:00 - 20:59 for Trip 1?

Best Regards,

Cherry

[JRe5777 · Accepted solution]

Hello @v-piga-msft  sorry for the delay, in the meanwhile I developed a solution.

What I meant was, for the first line the column trip duration = 18 minutes
And the trip started before 21h, so there were 15 minutes e that time frame and 3 minutes in the next time frame.

The way I did it, I don't know if it's the simplest but was the one I could remember, was to create calculated columns.
First of all, on power query I extracted the hour of departure and arrival (2 columns) and grouped them in the time frames I mentioned (00:00-05:59 | 06:00-20:59 | 21:00-23:59).

Then there were some calculated columns because I also had to divide between days (so for example, a trip starting today before midnight and ending tomorrow dawn was going to be divided in 2 time frames and in 2 days)
The code it's a bit complex and specific do some of the data conditions but I will still add a link for a word document and state this as the solution https://1drv.ms/w/s!AkP8HOhtvBzT4jgnTlVSlwxnonPf?e=T276py
Also, the information had to be in hours (not in minutes) so I had to divide minutes by 60 and seconds by 60^2.
There are 7 calculated columns, more or less one per page.

But thank you very much for your attention!