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]

what does each additional row mean? why is rate at 0:00-3:00 on row 2 different from other rows, etc?

 

[Roy_Excel_Island_Apps]

what does each additional row mean? why is rate at 0:00-3:00 on row 2 different from other rows, etc?

The colored table represents the percentage that has to be added to the hour worked value. Every row represents a day. Row 8 is a holiday. But that's not important now. I just want to know the hours worked between 00:00 and 03:00, 03:00 and 06:00,...

 

[awoohaw]

Some examples of the desired results would be helpful. You say the day and holiday don't matter, yet your question involve the wrapping of a day?

What % do you use for the start and/or end point? Are 3:00 and 6:00 both in the 3:00-6:00 bucket, I'm guessing the actual bucket ranges are 3:00:01 - 6:00:00, etc?

 

[Roy_Excel_Island_Apps]

True, an example would be helpful, so I made one.
Row 6 is where I need the calculations to be. Is this more clear?

 

[awoohaw]

True, an example would be helpful, so I made one.
Row 6 is where I need the calculations to be. Is this more clear?

So, according to that example, the person worked from midnight until 3:00 am, and the person came back at 20:00 and left at a time value that doesn't exist (midnight has a value of 0 in excel. Plus the day begins at 0:00 not at 1:00). And I'm assuming that your actual hour worked ends at the 59 second moment, not the 00 second.

I ask the first part because you have 8 days on your schedule, and the timecard has two days (B2:C2)

 

[Roy_Excel_Island_Apps]

So, according to that example, the person worked from midnight until 3:00 am, and the person came back at 20:00 and left at a time value that doesn't exist (midnight has a value of 0 in excel. Plus the day begins at 0:00 not at 1:00). And I'm assuming that your actual hour worked ends at the 59 second moment, not the 00 second.

I ask the first part because you have 8 days on your schedule, and the timecard has two days (B2:C2)

As you can see in cells B2:C3 this person worked over night. He did a shift from the 30th of April 18:00 pm until the day after, the 1st of May 05:00 am in the morning.
The schedule is a 24hours schedule, that calculates the hours worked, between timeslots.

 

[awoohaw]

As you can see in cells B2:C3 this person worked over night. He did a shift from the 30th of April 18:00 pm until the day after, the 1st of May 05:00 am in the morning.
The schedule is a 24hours schedule, that calculates the hours worked, between timeslots.

What if that shift went from a row 6 percent structure to a row 7 percent structure? How am I to know?
and again, the time value of 24 is non existent.

 

[Roy_Excel_Island_Apps]

What if that shift went from a row 6 percent structure to a row 7 percent structure? How am I to know?
and again, the time value of 24 is non existent.

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

 

[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

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