# Payroll timesheet with complex payrate structure needing formula variation

• 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

Thanks

AussieNat

• Re: Payroll timesheet with complex payrate structure needing formula variation

I suggest you to add two cells for filling the scenarios you suggested in the first point

Cell A = Shift Crosses midnight (Y/N)
Cell B = Day of the week/Public Holidays (DW/PH)
Cell C = Special Duties (Y/N)

And then draft an "if conditions" to calculate hourly rate in an independent cell entry

For shift crosses midnight covering both "day of the week" and "public holidays", I suggest the users to type two data entries for simplification.