# Calculate hours used between certain times and calculate break entitlement

#### tlg

##### New Member
Hello!

I would like some help from you Excel experts.

I have a staff rota / schedule, I have already included a formula which will calculate the total length of a shift using the formula:

=(IF(B2>C2,C2+1-B2,C2-B2))*24

This works great, however we have two different rates of pay depending on the times worked so day rate is hours worked between 05:00 and midnight, night rate is hours worked between midnight and 05:00. I would now like to automatically calculate how many hours were at day rate and how many hours were at night rate.

Next I would like to calculate the break entitlement based on the following rules which depend on the total number of hours worked, these rules are as follows:

4 hours or more but <6 hours = 15 minutes
6 hours or more but <8 hours = 30 minutes
8 hours or more but <9 hours = 45 minutes
9 hours or more but <11 hours = 1 hour
11 hours or more = 90 minutes

I have created a quick example (attached) of what I would like to achieve, the cells in white are the data I would enter (staff member name and their shift start and finish times), green cells already calculate as I would like and yellow cells are those which I could do with some help with.

Example Rota.xlsx
ABCDEFGH
1StaffStartFinishTotal HoursTotal Night Hours Between Midnight & 05:00Total Day Hours Between 05:00 & MidnightBreak Entitlement (Based On Total Hours)Total Paid Hours
2Staff Member 120:0007:0011561.59.5
3Staff Member 223:3006:006.551.50.56
4Staff Member 320:0008:0012571.510.5
5Staff Member 408:0017:0090918
6
74 hours or more but <6 hours = 15 minutes 6 hours or more but <8 hours = 30 minutes 8 hours or more but <9 hours = 45 minutes 9 hours or more but <11 hours = 1 hour 11 hours or more = 90 minutes
Sheet1
Cell Formulas
RangeFormula
D2:D5D2=(IF(B2>C2,C2+1-B2,C2-B2))*24
H2:H5H2=SUM(D2-G2)

Not sure how easily achievable the above is, I appreciate I may have made it more difficult by trying to do two different calculations so if you can only help with one then that is fine.

I would really appreciate any help I can get.

Thank you!

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Michael M

##### Well-known Member
In G2 and drag down as required
Excel Formula:
``=IF(D2<4,0,IF(D2<=6,0.25,IF(D2<=8,0.5,IF(D2<=9,0.75,IF(D2<11,1,1.5)))))``
Will look at the other prob after lunch, hopefully

#### Michael M

##### Well-known Member
In E2 and drag down
Excel Formula:
``=IF(C2>(--"05:00 AM"),5,INT(C2*24))``
AND in F2 and drag down
Excel Formula:
``=D2-E2``

#### Michael M

##### Well-known Member
Change to formula in E2
Excel Formula:
``=IF(B2<(--"12:00 pm"),0,IF(C2>(--"5:00 AM"),5,INT(C2*24)))``

#### Sulprobil

##### Board Regular

I suggest to use this approach which also offers break calculation:

#### tlg

##### New Member
Change to formula in E2
Excel Formula:
``=IF(B2<(--"12:00 pm"),0,IF(C2>(--"5:00 AM"),5,INT(C2*24)))``
Thank you very much for your help.

This seems to work apart from there seems to be an issue depending if it is 12 noon and 12 midnight.

When I enter the following:

12:00 16:00 - it correctly shows 4 hours total in D2, however in C2 it shows 5 which is incorrect, it should be 0 as the 4 hours worked are not between midnight and 5am.

I tried removing the PM and AM from the formula but this caused issues with other times.

Do you know how this can be corrected?

Thanks again.

#### Michael M

##### Well-known Member

UNTESTED
Excel Formula:
``=IF(B2<=(--"12:00 pm"),0,IF(C2>(--"5:00 AM"),5,IF(B2=(--"00:00 am"),INT(C2*24),INT(C2*24))))``

#### tlg

##### New Member
Perfect, thank you so much!

Replies
8
Views
104
Replies
0
Views
85
Replies
3
Views
770
Replies
8
Views
121
Replies
5
Views
50

1,140,995
Messages
5,703,619
Members
421,306
Latest member
ambuj Thakur

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back