Time sheet in Excel

    Hello i am trying to create a formula to calculate overtime in excel, the problem i am having is i need to calculate: total time, Regular Time and Overtime, but the overtime is only after 40 Regular time hours have been reached. i have it to where it will calculate overtime but it does it for any hours worked after 8. i just need after 40. i have a copy of the time sheet attached. thank you!!! need this ASAP!!! ALSO i need to keep the formula in total time to calculate from one day into the next, we work 24/7 365 so we can go from one day into the next on the same shift.

    Re: Time sheet in Excel


    Hello,


    As far as Total Time is concerned, you can test following formula :


    Code
    =MOD(E6-B6,1)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Time sheet in Excel


    saY the person has worked
    Monday- 7:00-23:00 16 hours
    Tuesday 23:00-7:00 8 hours (rolled into another day)
    Wednesday 15:00-7:00 16 hours (rolled into another day
    Thursday- 7:00-15:00 8 hours
    i need it to calculate the total hours for each day worked, regular hours for each day (8) and not add the overtime until the regular has reached 40

    Re: Time sheet in Excel


    Did you take a look at the sample file : Test Overtime ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Time sheet in Excel


    I did and it works but it takes the 16 hours and splits them, 8 in Reg and 8 in OT. i need it to keep the 16 in reg until reg reaches 40 then have it go into the OT

    Re: Time sheet in Excel


    After a couple of tests for cumulative weekly hours in cell O2 ... have adjusted the two basic formulas ...


    Hope this will help


    :smile:

    Re: Time sheet in Excel


    Quote from tasha2515;797413

    you are AMAZING!!!! thank you so very much!! its perfect


    Glad you could fix your problem ... :wink:


    Thanks a lot ... for your Thanks ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Re: Time sheet in Excel


    @ tasha2515


    What happens if the employee takes a 3 hour lunch?


    Based on the formula you have in your attached 'Copy of Express Time Sheet new 1.0 Master.xlsx' at another forum, regular time excludes the lunch break.
    The somewhat similar solution I posted 3 days ago at that forum took this into consideration.


    Had you cross posted in the appropriate manner, as advised on at least 3 forums, I wouldn't have wasted my time.


    Anyway, would you please now have the courtesy to go to all your cross posts and inform you now have a solution and give credit where credit is due.


    Thanks.

    Re: Time sheet in Excel


    NoSparks


    Would you mind indicating the links to all the cross posts ...


    Thanks

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

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

Register Yourself Login