I have been working on a new paysheet as my company is going from weekly to fortnightly pay for casual employees who work shift work.
I have almost conquered all problems however I still have a few issues I can't fix as the formulas I am using are becoming too long and the level this requires is above my capabilities.
Main issues are as follows:
- When a shift crosses midnight, the payrate can change, depending on the day of the week, public holidays and whether the employee is undertaking special duties (20%)
- When an employee works more than 3 hours they are required to take a break if they think they will work more than 5 hours, in some instances an employee will take a break but the job assigned will change and therefore work will finish early. In that case we must pay an employee 5 hours min if total hours work including break time equals 5 hours (ie. Person A works a total of 5.25 hours with a break of 30 mins = 4.45hours , we reimburse them 15 mins pay to 5 hours)
- I created an interim column "shift over midnight" to fix some of the point 1 problems but it is not workig for all scenarios
Is there a better way to create this template so my section heads can fill in our employees sheets?
In the timesheet I have entered scenarios that either do not calculate correctly or if you use the "shift over midnight" or "yes" (20%) text cell it changes data to incorrect calculations.
I have attached a spreadsheet with 1 timesheet - fully working (to the best of my capabilities)
If this thread needs to move to HIRE HELP - can you please advise estimate cost to look at solutions please
Any guidance or help is greatly appreciated