Help needed with Roster Calculations-Hours and overtime worked

R

ryanduddy123

New Member
Joined
Jun 5, 2014
Messages
1

<tbody>
[TD="class: xl26"]Date of Duty
[/TD]
[TD="class: xl37, colspan: 2"]Ordinary Tour[/TD]
[TD="class: xl28, colspan: 2"]Period of Extra Duty[/TD]
[TD="class: xl54, width: 152, colspan: 2"]Saturday[/TD]
[TD="class: xl31, width: 84"]Sunday[/TD]
[TD="class: xl33, width: 68"]P/Hol[/TD]
[TD="class: xl52, width: 112, colspan: 2"]Night Duty 8pm to 8am[/TD]
[TD="class: xl52, width: 141, colspan: 2"]Night duty 6pm - 8pm[/TD]

[TD="class: xl27, width: 64"][/TD]
[TD="class: xl27, width: 57"]From[/TD]
[TD="class: xl28, width: 53"]To[/TD]
[TD="class: xl28, width: 58"]From[/TD]
[TD="class: xl27, width: 55"]To[/TD]
[TD="class: xl32"]Rostered[/TD]
[TD="class: xl32"]Non Rostered[/TD]
[TD="class: xl32"]No. of Hours[/TD]
[TD="class: xl32"]No. of Hours[/TD]
[TD="class: xl32"]Rost[/TD]
[TD="class: xl32"]Non Rost[/TD]
[TD="class: xl32"]Rost[/TD]
[TD="class: xl32"]Non Rost[/TD]

[TD="class: xl29"][/TD]
[TD="class: xl29"][/TD]
[TD="class: xl29"][/TD]
[TD="class: xl29"][/TD]
[TD="class: xl29"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl25"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]

[TD="class: xl34"]09.06.14[/TD]
[TD="class: xl35, align: right"]7.00[/TD]
[TD="class: xl35, align: right"]17.00[/TD]
[TD="class: xl35"][/TD]
[TD="class: xl35"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl25"]10[/TD]
[TD="class: xl24"]1[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]

[TD="class: xl39, width: 844, colspan: 13"]In this line there was no overtime but it was a bank holiday so 10 goes into I column. 1 goes into 8pm 8am column J for 1 hour worked from 7am to 8am[/TD]

[TD="class: xl34"]10.06.14[/TD]
[TD="class: xl35, align: right"]7.00[/TD]
[TD="class: xl35, align: right"]17.00[/TD]
[TD="class: xl35, align: right"]6.00[/TD]
[TD="class: xl35, align: right"]20.00[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl25"][/TD]
[TD="class: xl24"]1[/TD]
[TD="class: xl24"]1[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"]2[/TD]

[TD="class: xl39, width: 844, colspan: 13"]Here there was an extra hour worked between 6am and the normal start time of 7am which equals 1 in column K. 1 hour between 7am and 8am which equals 1 in column J. And 2 hours overtime between 6pm and 8pm which equals 2 in Column M[/TD]

[TD="class: xl30"]11.06.14[/TD]
[TD="class: xl35, align: right"]12.00[/TD]
[TD="class: xl35, align: right"]10.00[/TD]
[TD="class: xl35"][/TD]
[TD="class: xl35"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl25"][/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"][/TD]

[TD="class: xl45, width: 844, colspan: 13"]Here there was just 10 hours with no overtime. So 2 hours that were rostered bwtween 8pm and 8am so 2 goes in column J[/TD]

[TD="class: xl30"]12.06.14[/TD]
[TD="class: xl35, align: right"]12.00[/TD]
[TD="class: xl35, align: right"]10.00[/TD]
[TD="class: xl35, align: right"]10.00[/TD]
[TD="class: xl35, align: right"]23.00[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl25"][/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"]1[/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"][/TD]

[TD="class: xl45, width: 844, colspan: 13"]Here I worked from 10am to 11pm so I worked 2 hrs overtime between 10am and 12pm and 1 hr overtime more between 10pm and 11pm. This results in 3 hours overtime. 2 hours in J for 8pm to 10pm. 1 in K for 10pm to 11pm which was not rostered and 2 in L for 6pm to 8pm[/TD]

[TD="class: xl30"]13.06.14[/TD]
[TD="class: xl35, align: right"]15.00[/TD]
[TD="class: xl35, align: right"]1.00[/TD]
[TD="class: xl35"][/TD]
[TD="class: xl35"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl25"][/TD]
[TD="class: xl24"]5[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"][/TD]

[TD="class: xl45, width: 844, colspan: 13"]These calculations get more complicated because we work 24hours. So for 3pm to 1am we get 5 hrs of 8pm to 8am allowance and 2 hours of 6pm to 8pm allowance both were rostered[/TD]

[TD="class: xl30"]14.06.14[/TD]
[TD="class: xl35, align: right"]15.00[/TD]
[TD="class: xl35, align: right"]1.00[/TD]
[TD="class: xl35, align: right"]15.00[/TD]
[TD="class: xl35, align: right"]3.00[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl25"][/TD]
[TD="class: xl24"]5[/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"][/TD]

[TD="class: xl45, width: 844, colspan: 13"]Here I worked 2 extra hours from 1am to 3am. We still enter the start time as 3pm but this means 2 non rostered 8pm to 8am hrs between 1am and 3am. So 5 8pm to 8am rostered hours between 8pm and 1am. And 2 rostered hours between 6pm and 8pm[/TD]

[TD="class: xl30"]15.06.14[/TD]
[TD="class: xl35, align: right"]17.00[/TD]
[TD="class: xl35, align: right"]3.00[/TD]
[TD="class: xl35"][/TD]
[TD="class: xl35"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl25"][/TD]
[TD="class: xl24"]7[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"][/TD]

[TD="class: xl45, width: 844, colspan: 13"]Here I started at 5pm and finished at 3am the next morning. This means 7 rostered 8pm to 8am hours.[/TD]

[TD="class: xl30"]16.06.14[/TD]
[TD="class: xl35, align: right"]15.00[/TD]
[TD="class: xl35, align: right"]23.00[/TD]
[TD="class: xl35, align: right"]23.00[/TD]
[TD="class: xl35, align: right"]3.00[/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"]8[/TD]
[TD="class: xl25"][/TD]
[TD="class: xl24"]3[/TD]
[TD="class: xl24"]4[/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"][/TD]

[TD="class: xl45, width: 844, colspan: 13"]Here I worked 3pm to 11pm. It is a Sunday so its 8 hours rostered so I need 8 in column H. There were 3 rostered 8pm to 8am hrs so 3 on column J and 4 non rostered 8pm to 8am hrs so 4 in column K. Also 2 6pm to 8pm hours so 2 in column L[/TD]

[TD="class: xl30"][/TD]
[TD="class: xl30"][/TD]
[TD="class: xl30"][/TD]
[TD="class: xl30"][/TD]
[TD="class: xl30"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl25"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]
[TD="class: xl24"][/TD]

[TD="class: xl45, width: 844, colspan: 13"]I know this is a complicated looking roster so it is way too complicated for me to do. The 24 hour working system, along with the allowances makes it quite tricky.[/TD]

</tbody>

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]-->[FONT=&quot]I would like to have a working excel sheet that calculates overtime and allowances from just entering the start and finish times per work day. I have looked at some examples and this is way over my head. I am using Windows 7 and Excel 2003.

There are 3 different shifts that are worked on any day. Early, late and night. Every tour is 10 hours except Sunday however we often do overtime which can be used as hours to be paid or hours used as toil. We also get 2 types of night allowances and the rates vary depending on whether those hours were rostered or overtime so for example.

1. First type of night allowance is given for any hours worked between 8pm and 8am, which is split into hours that were rostered and hours that were overtime.
2. Second type is for working between 6pm and 8pm again split into hours rostered and hours that were overtime. [/FONT]<!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->
 
Last edited:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
You must log in or register to reply here.

Similar threads

V
  • Locked
  • Question Question
If statement formula help between two times
Replies
2
Views
224
Fluff
Fluff
D
  • Solved
Timesheet Calculation
Replies
7
Views
1K
jorismoerings
J
W
  • Question Question
Employees per hour calculations
Replies
2
Views
352
Wgoldbaum
W
C
  • Question Question
Overtime Calculation
Replies
2
Views
480
ChetanPuri
C
JoeGraf10
  • Question Question
How to split hours worked into 3 shift patterns?
Replies
8
Views
3K
JoeGraf10
JoeGraf10

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

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
Back
Top