Calculate hours used between certain times and calculate break entitlement

tlg

New Member
Joined
Jan 27, 2011
Messages
6
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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,892
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Oct 27, 2005
Messages
20,892
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
Oct 27, 2005
Messages
20,892
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
Joined
May 12, 2020
Messages
160

ADVERTISEMENT

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

tlg

New Member
Joined
Jan 27, 2011
Messages
6
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
Joined
Oct 27, 2005
Messages
20,892
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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))))
 
Solution

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,892
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad to help...:cool:(y)(y)
 

Forum statistics

Threads
1,141,203
Messages
5,704,934
Members
421,372
Latest member
Jamie11

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top