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

Ordinary Hours must average 38 per week over 8 weeks
		- 8.15 hrs per day (standard)
		- 10 minutes sign on/off is counted towards ordinary hours
		- max shift of 12 hrs
		- min shift of 3 hrs
		- broken shift over 12 hours
		- 11 hrs break between shifts, if worked overtime must be paid for any ordinary hrs missed
Overtime, all time in access of ordinary 8 hrs and 9 min (8:15 as a fraction) in any shift	150% / 200%
	- first 3 hrs = 150% then 200% after
	- min of 2hrs at 200% if recalled to work after shift
Meal breaks are unpaid min 40mins max 50mins
Leave Loading					17.50%
Saturdays, this rate is in lieu of any shift loading or penalty	150%
Sundays, this rate is in lieu of any shift loading or penalty	200%
Public Holidays, this rate is in lieu of any shift loading or penalty	250%
	- To be paid if rostered to ordinarily work that day i.e. have worked that day 13 times in the past 12 months
	- Can substitute the day
Night Allowance between 7pm and 6am			15%
	- if working overtime - then pay only overtime
Casual Loading of 25% plus overtime loading		25%
	- subject to a 3 hr minimum shift
Higher duties after 2 hrs , for entire shift
	- if under 2 hrs then only pay time worked

<tbody>
</tbody>