+ Reply to Thread
Results 1 to 4 of 4

Calculating straight time and overtime

  1. #1
    Registered User
    Join Date
    11-14-2008
    Location
    Alaska
    Posts
    7

    Calculating straight time and overtime

    I'm pretty new at the whole Excel thing and many of the formulas are way over my head.
    I'm trying to figure out how to make a time sheet calculate straight time and overtime and I can not figure it out. The time sheet from the Excel Bible was close, but not quite what I'm looking for.

    I work seven rotating twelve hour shifts and anything over eight hours and anything over twelve hours is overtime. The shifts start at 0600 and 1800.

    I have several questions.
    1. how do I get the times to calculate? It works correctly when doing one shift (days), but not the other (night). It adds up to 24 on night shift.
    2. how do I make it seperate the twelve hours into 8 straight time hours and four over time hours?
    3. how do I get it to know when it has reached the 40 hour mark?
    Attached Files Attached Files
    Last edited by TMA; 11-14-2008 at 09:58 AM.

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi and welcome to the board
    It would be easier to help if you posted a small sample of your data ( see Thread answer - Go Advanced - Manage attachments) as an xls file
    Thx

  3. #3
    Registered User
    Join Date
    11-14-2008
    Location
    Alaska
    Posts
    7

    Please...

    I tried the custom formatting of the numers and I'm still doing something wrong

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    In C5 and copy across, =MIN("8:00", C4-C3 + (C3>C4) )

    In C6 and copy across, =MAX(0, C4-C3 + (C3>C4) - C5)

    J5: =SUM(C5:I5)

    K5: =MAX(0, C4-C3 + (C3>C4) - C5)


    L5: =J5+K5

    Format all time cells as [h]:mm
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1