# Need formula for calculating hours in timesheet

1. ## 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  Register To Reply

2. ## Re: Need formula for calculating overtime hours in timesheet

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

3. ## 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.  Register To Reply

4. ## 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  Register To Reply

5. ## Re: Need formula for calculating overtime hours in timesheet

That did the trick! Thank you very much!  Register To Reply

6. ## 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  Register To Reply

7. ## 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!  Register To Reply

8. ## 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,"")  Register To Reply

9. ## 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  Register To Reply

10. ## Re: Need formula for calculating hours in timesheet

Let me know if this is what you're looking for  Register To Reply

11. ## Re: Need formula for calculating hours in timesheet

That did the trick, thank you!  Register To Reply

12. ## 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?  Register To Reply

13. ## Re: Need formula for calculating hours in timesheet

SmAsSilk,  Register To Reply

14. ## Re: Need formula for calculating hours in timesheet

I did that after I had posted this........sorry newbie here........  Register To Reply