timesheet formulas

[andycreber]

Hi All,

Would someone be able to help me in designing a timesheet in excel, I do not know how to do the formulas for all the rules below. I guess to have seperate columns that splits the hours worked into the following categories and seperate columns for the rules?

I appreciate this is a lot of work, thank you to all and anyone that can help me in my hour of need.

Standard time
Overtime
Night allowance
Saturday penalty
Sunday penalty

weekly timesheets, Monday to Sunday.

rules are

[TABLE="width: 602"]
<tbody>[TR]
[TD="colspan: 6"]Ordinary Hours must average 38 per week over 8 weeks
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]- 8.15 hrs per day (standard)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]- 10 minutes sign on/off is counted towards ordinary hours
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]- max shift of 12 hrs
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]- min shift of 3 hrs
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]- broken shift over 12 hours
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]- 11 hrs break between shifts, if worked overtime must be paid for any ordinary hrs missed
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Overtime, all time in access of ordinary 8 hrs and 9 min (8:15 as a fraction)
in any shift
[/TD]
[TD="colspan: 2"]

150% / 200%
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]- first 3 hrs = 150% then 200% after
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]- min of 2hrs at 200% if recalled to work after shift
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Meal breaks are unpaid min 40mins max 50mins
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Leave Loading
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]17.50%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Saturdays, this rate is in lieu of any shift loading or penalty
[/TD]
[TD]150%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Sundays, this rate is in lieu of any shift loading or penalty
[/TD]
[TD]200%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Public Holidays, this rate is in lieu of any shift loading or penalty
[/TD]
[TD]250%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]- To be paid if rostered to ordinarily work that day i.e. have worked that day 13 times in the past 12 months
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]- Can substitute the day
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Night Allowance between 7pm and 6am
[/TD]
[TD][/TD]
[TD][/TD]
[TD]15%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]- if working overtime - then pay only overtime
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]Casual Loading of 25% plus overtime loading
[/TD]
[TD][/TD]
[TD]25%
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]- subject to a 3 hr minimum shift
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]Higher duties after 2 hrs , for entire shift
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]- if under 2 hrs then only pay time worked
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]