Posts by Excel Noob

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from Ranger

    Hi Excel Noob,


    Why don't you hide the 2 rows with the separate calculations as in the attached, then it shows the format you are looking for.


    Bill


    Bill,


    The problem with that is I send these timesheets on to my head office and if the values aren't visible then they will scratch their heads. Some of them are not the sharpest tools in the drawer so even if I tell them to unhide the rows I sure it will cause confusion.

    Re: Formulas For Calculating Time & Half And Double Time




    Bill,


    I see what you mean and if I use it I will carry the formula across. Thanks for your time.

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from StephenR

    On your sheet, try in Q35
    and in Q36



    Stephen,


    That looks like it should work but I still get the value warning as a result. I have read that you are supposed to hit Ctrl, Shift & Enter all at the same time to enter an array formula but it doesn't appear to work. I copied the formula you supplied and paste it into my worksheet but as I said it comes up with the value error. :confused:

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from Ranger

    Hi Excel Noob,


    I have added the formulas to the bottom of your spreadsheet, they seem to work ok


    Bill



    Thanks Bill,


    If all else fails then I will have to break it out and add the rows like you did. I would much rather work within the existing format as it is cleaner but if it can't be done then it is what it is. Thanks much.

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from StephenR

    Let you off then. Ranger's provided you with a solution but you could just amend the formulae to:


    Well I tried the formula and changed the cells referenced to D33 to S33 and it comes up with a value warning. The problem here is that the days worked will change from week to week so the overtime values will be anywhere from D33 to S33. I need the formula to work within the timesheet without breaking it into other columns like Ranger made. These are timesheets that my employee's fill out and e-mail me every week. The numbers at the top of the sheet represent days of the month. Whatever date it is that's the column where they enter their time worked.


    Not trying to be difficult and I really do appreaciate all of your efforts

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from StephenR

    Well, that was based on the info you supplied in the first post.


    Sorry Mate I'm a Noob:p I did say that the OT was calculated in a seperate box though ;)

    Re: Formulas For Calculating Time & Half And Double Time




    Thanks for the formula but it appears that you are factoring in the 8 hours of regular time. That is completely removed from the forula because I am already showing the OT as a seperate value. I have attached a file on a earlier post.

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from ByTheCringe2

    Welcome to the forum. Can you attach a sample workbook showing the required outputs?


    Thanks for the welcome here is a sample

    Re: Formulas For Calculating Time & Half And Double Time


    Quote from StephenR

    You'll have to explain when overtime kicks in, i.e. where does your 17/8.5 hours come from?


    The overtime is already totalled and it's in a seperate box. 8 hours is a regular day. The first 4 hours after the 8 is 1.5x and anything beryond that is 2x.
    14 hour work day 8 hours of regular 4 hours of 1.5x and 2 hours of 2x = 14hours.


    As I said I do not need to calcualte the regular time as I have already seperated the regular time from the overtime. D33 to S33 only contains the total overtime hours worked for each day.
    Thanks for the quick response.

    Hi I'm new to the site so be gentle : D


    I have a time sheet for my employee's that I need to calculate their overtime in 1.5x and 2x rates.


    Their overtime totals are done in individule columns from D33 to S33. The first 4 hours per day are charged at 1.5x and anything over that is 2x. I want to show the 1.5x in one box and the 2x in another. I do believe that I need two formulas one in each of the boxes where the final totals would go.


    Here's an example, in columns D37 to D41 the employee has worked 12, 14, 9, 16 and 14.5 hours. so that's 17 hours @ 1.5x and 8.5 @ 2x. Hope this makes sense.:confused: