Calculate the working hours where a worker's log in and log out times span multiple shifts:

[Applicable88]

Hello, 

I have following shift times:

Shift		From		Until
Early		06:00		14:00
Late		14:00		22:00
Night		22:00		06:00

I want to calculate the hours someone works in his shift. But also when he might has another working model, which can span over

different shift, I want to know the respective hours calculated for those shift:

WorkDay		WorkerID		LogInTime		LogOutTime		Early	Late	Night
21.03.2023		A		21.03.2023 05:30:00		21.03.2023 12:00:00		6,00	0,00	0,50
21.03.2023		B		21.03.2023 12:00:00		21.03.2023 15:00:00		6,00	1,00	0,00
21.03.2023		C		21.03.2023 18:00:00		21.03.2023 23:00:00		0,00	4,00	1,00
21.03.2023		D		21.03.2023 06:00:00		21.03.2023 14:00:00		8,00	0,00	0,00

I basically need the red columns where I can summarized the hours which spans over two or more shift. WorkerID = D is for example someone who entirely stays with his shift. But not the others. They always work far into the other shifts and I want those working hours listed separately.

Thank you very much in advance.

Best. 

[Idrissshatila · Accepted solution]

Hello @Applicable88 ,

Check if this video would help https://youtu.be/6JA6uy3ZRBA

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Applicable88]

@Idrissshatila thank you very much. I think I can work it out with that method.