Calculating overtime in Excel

    Hi,
    I`m trying to calculate overtime when I have more than 40 hours per week.
    I have an spreadsheet where I enter the data like start time, end time and brake as result I have for example 57:00 Hours per week and need to calculate 40 hours per regular rate and the hours over 40 by 1.5.
    So far tried
    A1 = 57:00 - total hours
    In cell B1 I`m using IF function: IF A1>40; total hours-40;else =0 this way i will get overtime hours or 0 if there is no overtime =17 in current example. Then in next cell C1 =A1-B1 = 40 from here I can multiple the overtime hours and regular hours the way I want.
    The problem here is A1 has value 57:00 and I don`t know how to proper enter the if function =IF(A1>40;A1-40;0) where 40 is regular hours. A1 is 57:00 I've tried with 40 and 40:00 but can't get any result.
    How to enter this 40 to be like hours?


    Thanks for your time and help!

    Re: Calculating overtime in Excel


    You need to change the Cell formatting for G4 and G5 to general and then enter the below formulas;


    G4=IF(TEXT(E11,"[hh]")>=40,40,TEXT(E11,"[hh]"))
    G5=IF(TEXT(E11,"[hh]")>40,TEXT(E11,"[hh]")-40,0)


    Dan

    [SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

    Re: Calculating overtime in Excel


    You're welcome.

    [SIZE=1]It's like asking a mechanic to fix your car, without actually taking your car to him. Post your code/file and you'll get much quicker and more accurate solutions to your problem.[/SIZE]

    Re: Calculating overtime in Excel


    Hi Dan,
    Just found a problem in the sheet.
    When I have 40 h 05 min it doesn't calculate them right.
    Also if I have less than 40 for example 39 hours it's like 39h x $1 then -1h overtime = $39 - $1.5 overtime h.
    Can yo help me with this please?
    Thanks!

    Re: Calculating overtime in Excel


    Hi Dan,
    Sorry for my late reply and thanks for trying to help me.
    Seems like if someone worked just 2 day 10 hour per day he still get 4 hours overtime even if the person worked less than 40 hours.
    Also if the person worked 5 days in the week 8 hours per day and for example he worked one more day for 6 hour this 6 hours are calculated regular hours not overtime hours.
    I still believe it should be easiest way to calculate only overtime hours worked over 40 hours.

    Re: Calculating overtime in Excel


    Quote from mike2000;681409

    Hi Dan,
    Sorry for my late reply and thanks for trying to help me.
    Seems like if someone worked just 2 day 10 hour per day he still get 4 hours overtime even if the person worked less than 40 hours.
    Also if the person worked 5 days in the week 8 hours per day and for example he worked one more day for 6 hour this 6 hours are calculated regular hours not overtime hours.
    I still believe it should be easiest way to calculate only overtime hours worked over 40 hours.


    It may be different where you come from, but here in Australia, if you work more than 8hrs in a single day, the "overtime" hrs are calculated at an overtime rate. Even if you work less than 40 hrs per week (unless you are paid a casual wage which overtime hrs are not always applied to).


    In the case that you have worked a 6th day in a week (ie. a day on the weekend), all hours on that day would be considered overtime.


    How do you want it calculated? The attachment treats Saturday and Sunday as entirely overtime.


    forum.ozgrid.com/index.php?attachment/55991/

    Re: Calculating overtime in Excel


    Hi,
    Thanks for your reply.
    I`m in USA and here overtime paid depends of the company but in general all hours over 40 are considered overtime and paid 1.5 times than regular wage.
    So I just need to calculate the hours and find formula to sum the wage for the regular hours and then for overtime hours.
    At my first post I have attached an sample of what I have tried to do but unfortunately couldn't make it to work.

    Re: Calculating overtime in Excel


    I found that I can enter total hours and =IF(R3>40, ((R3-40)*1.5*R2+R2*40), (R2*R3)) when R2 is pay rate and R3 is weekly hours

Participate now!

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

Register Yourself Login