How to calculate regular and overtime on a timesheet for excel...

I have a timesheet I am trying to create.

Format, Custom HH:MM

Cell A1 8:30 am (start time)
Cell B1 6:30 pm (end time)
Cell D1 12:00 pm (lunch start)
Cell E1 1:00pm (lunch end)

I want F1 to show regular time up to 8 hours (no more)
I want G1 to show overtime, over 8 hours. (no limit)

F1 should read 8 hours and G1 should read 1 hour if this works as I want.

Anyone know of a creative way to do this?

Thanks!

in f1 put:

Formula:

Please Login or Register  to view this content.

IN G1 put:
=IF(F1<8,0,((HOUR(B1)-HOUR(A1))-(HOUR(D1)-HOUR(C1)))-8)

---------- Post added at 03:08 PM ---------- Previous post was at 03:07 PM ----------

NOTE: I used Military time. Not sure if it would work any other way.

Epic, thank you!

I tried this with minute increments (if Cell B1 reads 6:00 PM cell F1 regular time would be 8, but G1 overtime would be 0.5HR)

It seems if I put in 6:00, it will show still 1 hr overtime, and if I put in 5:59, it puts in zero...

Do I need to add a formula to add in the minutes as well? I'm don't want to calculate on the seconds...

Thanks!

I should have known you would run into issues. Anyways, this is a rather generic request, and your best bet is to search online for your answer. A quick google search gave me this site, which wiill give you what you want and more:

HTML Code: 

You can even download a workbook with all of the formulas.

Hi unclejemima

Another option would be
In cell F1: =IF((B1-E1)+(D1-A1)>L1,L1)
In cell G1: =IF((B1-E1)+(D1-A1)>L1,((B1-E1)+(D1-A1))-L1)
In cell L1, I put 8 hours for your regular hours and formatted cells F1, G1, L1 as [h]:mm

Thanks guys. Forgot to say this work. Thread solved!