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



  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!