Calculate Hours between timeslots

[Roy_Excel_Island_Apps]

Hi all,

in the image you can see a template that can be filled in by an employee. He has to fill in when he has done an on-call intervention. On the right (in yellow) I want to calculate the hours worked in the timeslots in S1 to AB1. If you have done an intervention from 13:00h-20:00h, I get 2 hours in 12:15h timeslot, 3 hours in the 15-18h timeslot and 2 hours in the 18-20h timeslot.

I need to know the hours worked in the timeslots, because they need to get paid to a certain percentage, depending on the timeslots they worked in. The percentages are in the colored schema. I will manage to calculate it when I have the hours per timeslot. But it's too difficult for me. When working in the same they: OK
But it get's difficult when someone does an intervention over midnight, like in the image. When working from 22h00-02:00h, I need to see 1 hour in the 20-23h timeslot, 1 hours in the 23-24h timeslot and 2 hours in the 00-03h timeslot. But how?

Formula now in cel T12: =IF(MIN(HOUR($Q$14);HOUR(T1))-MAX(HOUR($Q$13);HOUR(S1))>0;MIN(HOUR($Q$14);HOUR(T1))-MAX(HOUR($Q$13);HOUR(S1));0)

Thanks so much for the one who can help me!

Kind regards, Roy

 

[awoohaw]

There are no percentages in this sheet. These are all hours.
In these example the person worked 2 hours in the 18-20h frame, 3 hours in the 20-23h frame, 1 hour in the 23-24h frame, 3 hours in the 00-03h frame and 2 hours in the 03-06h frame. I need a formula to calculate these hours in the frames.
B2-C3 are parameters

my understanding is that you are trying to assign an hour percentage bump to hourly increments on specific days of the week (or a holiday). Is that correct?

 

[Roy_Excel_Island_Apps]

then you have completely changed your question:
what are the numbers below in this image with "%" signs after them?

View attachment 110737

I will use these to multiply the worked hours with. Don't mind these percentages. Just look at the last picture. Excuse me for the inconvenience.

 

[awoohaw]

I will use these to multiply the worked hours with. Don't mind these percentages. Just look at the last picture. Excuse me for the inconvenience.

Sorry, I cannot think that way. Regardless of percentages being used now, why do you have 8 rows in post #1 that now that do not matter.
What is it you really want to get to at the end of all of this? Again, are you trying to calculate hours worked in specific time ranges on specific days (or holiday)?

 

[Roy_Excel_Island_Apps]

Sorry for the misunderstanding. These percentages are used to multiply them with the worked hours. But they don't matter now. I just need a formula to calculate the worked hours between 00-03h, 03-06h,...