Timesheet: Calculate Overtime Rates crossing into next day

  • Hi,

    This is my first post in this forum and I've got myself doozy of a problem

    I'm busy recreating our overtime claim form and had some success with the calculation of breaks from overall time.
    The problem i'm now facing is how to calculate hours on adjusted rate..
    I have an idea what to with Min/max to determine a time, I hit a brick wall when a shift starts in normal time, crosses all of ajusted rate, and ends in normal time again.

    Attached is a copy of the test sheet - I have tried a few approaches as some of the red values show, and some of the test values I randomly have on the right-hand side outside of the sheet.

    The conditions are:

    Automatically deduct 30 minutes for every 6hrs+ worked (mandatory break) per time in/out. (working)
    Show total hours worked below each day (working)
    Calculate adjusted rate for hours worked between 8pm-6am (NOT WORKING)
    EDIT: Times must be written in the same day as Start/Finish, allowing for cross from one day to another

    Same conditions apply for weekend work


    I wouldn't be asking is I hadn't searched google and numerous excel-related sites for an obvious solution. A lot of the formulas used have been based on answers found elsewehere but i have reached an impasse with my current knowledge,
    Where possible, I'd like to avoid using VBA,
    It really is driving me to distraction, so any help or pointers in the right direction would be appreciated :)

  • Re: Timesheet Differential Trauma..

    Thank you for the reply and example sheet - this will take me a while to get my head around all of the references you created, as the use of Name Manager in formaulas is all new to me :)
    One thing I did test was night shift work which crosses from one day into the next, as this was the issue causing me no-end of headache..

    As an example someone may work a shift from 7pm Mon until 7am Tue (eg Security) and not take any long breaks worth noting. They would record this simply as a start time and finish time under Mon_Sta and Mon_Fin - 19:00, 07:00

    So.. start time begins in normal time, shift goes all the way through overtime, and ends in normal time again, allowing the sheet to automatically calculate normal time, overtime and mandatory break.

    I know this is staying into becoming a tutorial, but how would I account for this?
    A breakdown of the references you created would also be very helpful in getting me to understand the formulas, as the calculated values need to be blanked out (using If statements) until the cells they are reading from have values in them :)

    Thank you again.

  • Re: Timesheet: Calculate Overtime Rates crossing into next day

    Herbds7 - again, thank you for helping resolve my problem!

    I applied the graveyard shift working to the first row of times, and formatted the table to look like our corporate timesheet.

    I tried creating a copies of the table to replace the remaining weeks in the sheet, however the formulas you have created reference the original table
    eg. =ROW()-MIN(ROW(Table1))+1

    Is there a way to reference a table as a variable, by obtaining the active table name (eg week 1, week 2)?
    If it is not possible to do this without VBA I'd still be interested in creating a function to do the job :)

  • Re: Timesheet: Calculate Overtime Rates crossing into next day

    Thank you very much for the help :)
    I was able to use the formulas you provided and create a version of the sheet compatible with Excel 2003 upwards.
    Here's a copy in case it is of use to anyone. It is protected with editable cell ranges hidden formula cells, but no password has been set if you wish to explore.

    Regular time and Overtime time values can be changed through Name Manager (2007, 2010), or through Insert > Name > Define (2003)

Participate now!

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