+ Reply to Thread
Results 1 to 7 of 7

Time sheet hours and overtime calculations

  1. #1
    Registered User
    Join Date
    11-13-2013
    Location
    Northampton, UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Time sheet hours and overtime calculations

    Hi.

    I need some help please creating a time sheet to record hours and overtime calculations based on inputting staff start and finish times.

    A1 = Name
    B1 = Shift start (Can be anything between 00:01 and 23:59)
    C1 = Shift finish (Shifts can start and finish on the same day, or start day one and finish day two)

    Formulas required for the following...
    D1 = Total hours worked for this shift
    E1 = Hours worked between 0630-1830
    F1 = Hours worked for this shift between 1830-0630

    Many thanks.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,148

    Re: Time sheet hours and overtime calculations

    Hi,

    One way


    D1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    E1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    F1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor amit.wilson's Avatar
    Join Date
    07-09-2013
    Location
    Gotham
    MS-Off Ver
    Excel for Mac 2011
    Posts
    283

    Re: Time sheet hours and overtime calculations

    Richard,

    there may be a few problems with this solution:

    1. In places like C1-TIME(18,30,0), C1 is a Date+Time value.

    2. E1 also needs to account for the shift ending before 6.30 pm.

    Have made some changes.

    E1:
    =MIN((DATE(YEAR(C1),MONTH(C1),DAY(C1))+(TIME(18,30,0))),C1)-MAX(DATE(YEAR(C1),MONTH(C1),DAY(C1))+TIME(6,30,0),B1)

    F1:
    =D1-E1

    What do you think? The E1 formula look long and ugly now. :-) Is there a more elegant solution?

    Cheers

  4. #4
    Registered User
    Join Date
    11-13-2013
    Location
    Northampton, UK
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Time sheet hours and overtime calculations

    Many thanks for the comments so far.

    I tested the latest suggestion from amit.wilson and it works OK except for when the finish time is after mid-night in which case it returns negative numbers.

    Any ideas...?

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,148

    Re: Time sheet hours and overtime calculations

    [QUOTE=amit.wilson;3474358]Richard,

    there may be a few problems with this solution:

    1. In places like C1-TIME(18,30,0), C1 is a Date+Time value.

    2. E1 also needs to account for the shift ending before 6.30 pm.


    Re 1. I had rather assumed that B1 & C1 are both time numbers, i.e. less than 1 and don't include a date number.

    Re 2. Good catch!

    Hence
    E1 now:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and F1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Regards

  6. #6
    Forum Contributor amit.wilson's Avatar
    Join Date
    07-09-2013
    Location
    Gotham
    MS-Off Ver
    Excel for Mac 2011
    Posts
    283

    Re: Time sheet hours and overtime calculations

    sparkyw,

    when the finish time is after mid-night in which case it returns negative numbers.
    Sorry should have tested for that.

    Have changed things a bit. Try this:

    D1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    E1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    F1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This will breakdown for shifts longer than 24 hours... as will the employees. So am assuming that's not a problem. :-)

    Cheers

    PS: Richard, we'll need to include the date as sparkyw said
    (Shifts can start and finish on the same day, or start day one and finish day two)
    <-- If you're happy & you know it...click the star.:-)

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,148

    Re: Time sheet hours and overtime calculations

    Quote Originally Posted by amit.wilson View Post
    sparkyw,


    PS: Richard, we'll need to include the date as sparkyw said
    @amit

    Hi,

    That would certainly be the case for a shift that lasted longer than 24 hours in case it stretched into a 3rd day, but since the implication was that it starts on one day and ends no later than the second day and is presumably less than 24 hours the date is not absolutely necessary. The formula can take a cross midnight shift into account by testing whether the end time is less than the start time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Subtracting hours from time to calculate overtime hours
    By nabilishes in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-16-2012, 08:56 AM
  2. Time Calculations:overtime
    By lovinguy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2011, 08:50 AM
  3. Normal time / Overtime calculations
    By rwgrietveld in forum Tips and Tutorials
    Replies: 1
    Last Post: 02-19-2010, 02:22 PM
  4. Replies: 4
    Last Post: 01-03-2009, 03:28 PM
  5. reg time, overtime doubletime calculations
    By susanjb in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-04-2005, 12:37 AM

Tags for this Thread

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