Calculate working time by shifts - sum hours by shifts by calendar day

[gvlado]

Dear all 
I couldn't find a solution for calculating working time in the shifts.
For example how many hours in which shift bus and driver were working.

I would be happy to get some knowledge / solution.
Here it is excel table

Bus and drivers table
#	Bus	Driver	Start date	Start time	End date	End time	Note
1	B01	Jim	2022-01-03	04:00	2022-01-04	00:00
4	B01	Steve	2022-01-05	05:00	2022-01-05	16:00
2	B02	Mike	2022-01-02	07:00	2022-01-02	18:00
3	B02	Steve	2022-01-06	09:00	2022-01-07	00:10
5	B03	Mike	2022-01-07	05:00	2022-01-09	02:00
	Shift definition table
	#	Shift	Start	End			Note
	1	Shift 1	07:00	15:00			Date start on hour 00:00
	2	Shift 2	15:00	23:00
	3	Shift 3	23:00	07:00
Desired table 1
#	Bus	Date	Shift 1	Shift 2	Shift 3	Total	Note
1	B01	2022-01-03	08:00	08:00	04:00	20:00	Shift 3= 3 h in morning + 1 h at evening
2	B01	2022-01-04	08:00	08:00	08:00	24:00	Shift 3= 1 h at evening + 7 h in the mornng
3	B01	2022-01-05	08:00	01:00	02:00	11:00	Shift 3 = 2 h in the morning
4	B02	2022-01-02	08:00	03:00	00:00	11:00	Shift 3= 0 in the morning and 0 at evening
5	B02	2022-01-06	06:00	08:00	01:00	15:00	Shift 3= 1 h at evening
6	B02	2022-01-07	00:00	00:00	00:10	0:10	Shift 3= 10 min in the morning
7	B03	2022-01-07	08:00	08:00	03:00	19:00	Shift 3= 2 h in morning + 1 h at evening
8	B03	2022-01-08	08:00	08:00	08:00	24:00	Shift 3= 1 h at evening + 7h in the mornng
9	B03	2022-01-09	00:00	00:00	02:00	2:00	Shift 3 = 2 h in the morning
	Subtotal		54:00	44:00	28:10	126:10
Desired table 2
#	Driver	Date	Shift 1	Shift 2	Shift 3	Total	Note
1	Jim	2022-01-03	08:00	08:00	04:00	20:00	Shift 3= 3 h in morning + 1 h at evening
2	Jim	2022-01-04	08:00	08:00	08:00	24:00	Shift 3= 7 h in the morning +1 h at evening
3	Steve	2022-01-05	08:00	01:00	02:00	11:00	Shift 3= 2 h in the morning
4	Steve	2022-01-06	06:00	08:00	01:00	15:00	Shift 3= 1 h at evening
5	Steve	2022-01-07	00:00	00:00	00:10	0:10	Shift 3=10 min in the morning
6	Mike	2022-01-02	08:00	03:00	00:00	11:00	No Shift 3
7	Mike	2022-01-07	08:00	08:00	03:00	19:00	Shift 3= 2 h in morning + 1 h at evening
8	Mike	2022-01-08	08:00	08:00	08:00	24:00	Full day
9	Mike	2022-01-09	00:00	00:00	02:00	2:00	Shift 3= 2 h in the morning
	Subtotal		54:00	44:00	28:10	126:10

[vanessafvg]

Its a little confusing your logic for the shift time tables.  What is the business rules here?  Can you explain more about the 3 shifts?

i would join the date and time and use a datediff in a calculated column to get each total hours.  

however the shift aspect is not easy to work out with the information given , what is the rule for the shifts.  There seeems to be overlapping information which doesn't make sense with what you have given.

i created a power bi file, maybe this can help.

[gvlado]

Thanks @vanessafvg for your effort
Little explanation for shifts
Calendar day is from 00:00 hours to 24:00 (23:59) hours
Working shifts are divided into 3 parts that are spread over two calendar days (3 times 8 hours)
Shift 1 starts in the morning (07:00) of the calendar day and lasts until 15:00
Shift 2 starts at 15:00 of the calendar day and lasts until 23:00
Shift 2 starts at 23:00 of the calendar day and lasts until 07:00 the next calendar day.
So DATEDIFF is not working, and that is the problem
It should be some kind of time difference that will cover two calendar days - for example from 23:00 today until 01:00 tomorrow should be 02:00 hours and zero minutes.

[vanessafvg]

did you look at the file i created?  i combined date and time so that should resolve that issue.  please see the file i created and the measure hours and let me know what in it specifically is wrong.  

[gvlado]

Your pbx is ok but it doesnt give me answer by shifts.
See Desired tables
Should give information about how many hours bus did in first, second and third shift that calendar day.
(Bus 01, on date YYYY-MM-DD, on Shift X = hh:mm, and total hours for this calendar day)
I think the problem is how to divide one calendar day in 3 parts (shifts)
Thanks for your effort - it is not easy

[vanessafvg]

yes but what is complicated to undersand is how one bus can have 2 shifts at the same time, in order to do this cleverly you need to provide those shift patterns.  you have the same bus at shift 1 and shift 2 at the same time, which doesn't make sense.  Can you explain? 

[gvlado]

Simple -> see desired table
For example :
Due to I pay different amounts for different shifts I have to know how many working hours and minutes the driver was working in which shift.
But don't bother if it is too complicated! Hope I will find another way with "IF" (hh:mm<00:00 and >=23:00) then Shift3 .... to get a new pattern and then ... something 
I still don't have clear idea 
The point is to get from Bus and driver table -> the Desired table by the rule of the Shift table