Help needed with Roster Calculations-Hours and overtime worked

[ryanduddy123]

Date of Duty	Ordinary Tour	Period of Extra Duty	Saturday	Sunday	P/Hol	Night Duty 8pm to 8am	Night duty 6pm - 8pm
	From	To	From	To	Rostered	Non Rostered	No. of Hours	No. of Hours	Rost	Non Rost	Rost	Non Rost
09.06.14	7.00	17.00						10	1
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
10.06.14	7.00	17.00	6.00	20.00					1	1		2
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
11.06.14	12.00	10.00							2		2
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
12.06.14	12.00	10.00	10.00	23.00					2	1	2
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
13.06.14	15.00	1.00							5		2
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
14.06.14	15.00	1.00	15.00	3.00					5	2	2
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
15.06.14	17.00	3.00							7		2
Here I started at 5pm and finished at 3am the next morning. This means 7 rostered 8pm to 8am hours.
16.06.14	15.00	23.00	23.00	3.00			8		3	4	2
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
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.

<tbody>
</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]-->