Share via

Excel Time Sheet

Anonymous
2022-07-26T20:51:59+00:00

I am creating a time sheet that displays Date, Clock in time, Clock out time, Total hours for that day, regular hours for that day, and overtime hours for that day. I need a formula to compute the regular hours and overtime hours. Overtime is computed as anything over 8 hours each day as well as anything over 40 regular hours in each week. For example, if I work 10 hours for 4 days and take 1 day off then work 10 hours for day 6 & 7... then I have 8 regular hours plus 2 OT Hours for days 1,2,3,4,6 and 10 OT hours for day 7. So the weekly OT rule only kicks in when I have at least 40 hours of regular time.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

11 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2022-07-27T06:51:28+00:00

    1 person found this answer helpful.
    0 comments
  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2022-07-27T14:31:13+00:00

    Thank you for your help. But how do I also add the Overtime for anything over 40 regular hours worked in addition to the 8 hours per day?

    My file already calculates that, the left black table shows the overtime per day and the right blue table calculates the total and overtime per week.

    Please look into the sheet setup where you can specify different timings, lunch, holidays etc.

    If the result is not what you expect, download my file, make your settings, fill in some times, share that file and tell me what you expect.

    Andreas.

    0 comments
  3. Anonymous
    2022-07-27T13:21:01+00:00

    Thank you for your help. But how do I also add the Overtime for anything over 40 regular hours worked in addition to the 8 hours per day?

    0 comments
  4. Anonymous
    2022-07-27T09:00:12+00:00

    Hi, Tony. Thank you for your question and reaching out. My name is John and I’d be more than happy to help you with your query.

    I will send you the article that I guides me when I had the same concern. I believe it will be easier for you to check https://www.extendoffice.com/documents/excel/4281-excel-calculate-overtime.html on your end.

    This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products).
    Thoroughly research any product advertised on the site before you decide to download and install it.'

    Best regards,
    John

    0 comments
  5. Anonymous
    2022-07-27T00:06:19+00:00

    HI Tony,

    First of all, you need to find the hours worked by the employee. For determining this, just use the following formula. For example, = B11-A11
    Here, B11 is the starting cell of the ending time and A11 is the starting cell of the starting time.

    The next step is to utilize the TIME function on your next cell which is going to the named overtime. For example, my next cell is going to be C11.
    Insert the formula =C11-TIME(8,0,0) for an overtime after 8hours and it will give you the hours of overtime.
    Hope this helps. Keep us posted on the status for further assistance.

    Regards,

    Ademola

    0 comments