+ Reply to Thread
Results 1 to 14 of 14

Need formula for calculating hours in timesheet

  1. #1
    Registered User
    Join Date
    01-20-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    13

    Thumbs up Need formula for calculating hours in timesheet

    I am putting together a time sheet, and I am trying to figure out a formula for calculating overtime hours. Anything over 8 hours is considered overtime, and I would like those hours to appear in a different cell.

    In my example (attached), you can see that the hours for 1/20 are over 8 hours, and 10 hours is showing in the "Total Regular Hours" column. I would like for all hours entered in this column to stop at 8, and any hours over 8 would show in the "Total Overtime Hours" column. (In this case, there should be an '8' in the "Total Regular Hours" column and a '2' in the "Total Overtime Hours" column.)

    I've tried a few different options, but I can't figure out what the formula would be to achieve this result. Any help would be greatly appreciated!Timesheet Worksheet.xls
    Last edited by alannanoelle; 01-20-2012 at 05:44 PM. Reason: Another problem

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Need formula for calculating overtime hours in timesheet

    Something like? =IF(F8>TIME(8,0,0),F8-TIME(8,0,0),"")

  3. #3
    Registered User
    Join Date
    01-20-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Need formula for calculating overtime hours in timesheet

    I tried that formula, but it gave me the result of 0.08 instead of 2. I think the beginning of the formula may be correct, but it seems like something is missing.

  4. #4
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Need formula for calculating overtime hours in timesheet

    oh. I didn't realize I was in the time format. In that case =IF(F8>TIME(8,0,0),F8-TIME(8,0,0),"")*24

  5. #5
    Registered User
    Join Date
    01-20-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Need formula for calculating overtime hours in timesheet

    That did the trick! Thank you very much!

  6. #6
    Registered User
    Join Date
    01-20-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Need formula for calculating overtime hours in timesheet

    Okay, I spoke too soon - I copied the formula down and put in new time values, but the formula is giving me weird results for the rest of them. Please take a look at the attached spreadsheet.
    Timesheet Worksheet.xls

  7. #7
    Registered User
    Join Date
    01-20-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Need formula for calculating overtime hours in timesheet

    Sorry, I figured it out - I needed to change the formatting of the cells from a time to a number.
    Thanks!

  8. #8
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Need formula for calculating overtime hours in timesheet

    Haha... i put the 24 in the wrong place. Oops.

    =IF(F10>TIME(8,0,0),(F10-TIME(8,0,0))*24,"")

  9. #9
    Registered User
    Join Date
    01-20-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Need formula for calculating hours in timesheet

    I found another issue with my timesheet. The formula above works, but I've realized that the results in column F are in a time format, and the results in column G are in a number format. I would like for both columns F and G to be in a number format. I tried just changing the cell format, but the sum is coming up as a strange decimal rather than the actual number of hours.

    Also, column F should not show anything over 8 hours - anything over 8 is reported in column G. Is there a way to get column F to stay at 8 when overtime is calculated in column G?

    I've attached the current worksheet I'm using:
    Timesheet Worksheet.xls

    Thanks in advance!

  10. #10
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Need formula for calculating hours in timesheet

    Let me know if this is what you're looking for
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-20-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Need formula for calculating hours in timesheet

    That did the trick, thank you!

  12. #12
    Registered User
    Join Date
    03-25-2013
    Location
    Phoenix
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Need formula for calculating hours in timesheet

    I'm having a similar issue with our timesheet as well and I just can't seem to get it corrected. The issue is when someone works less that 8 hrs in the day. Can you take a look at this for me?
    Attached Files Attached Files
    Last edited by SmAsSilk; 03-25-2013 at 01:34 PM.

  13. #13
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Need formula for calculating hours in timesheet

    SmAsSilk,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  14. #14
    Registered User
    Join Date
    03-25-2013
    Location
    Phoenix
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Need formula for calculating hours in timesheet

    I did that after I had posted this........sorry newbie here........

+ 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