Timesheet maker

[Lionround]

Excel 2007

I am trying to make a timesheet for our office on which you can enter your time on a daily basis. My problem is that my formula returns the number of hours worked (that sounds like a good problem to most of you); however, if it more than 8 hours, I need it to return 8 in the hours worked column and the remainder in the OT hours column.

My formula thus far in cell E13:

=IF(ISERROR((IF(AND(C13<>0,D13<>0),IF(D13<C13,D13+1-C13,D13-C13),""))*24-0.5),"0",((IF(AND(C13<>0,D13<>0),IF(D13<C13,D13+1-C13,D13-C13),""))*24-0.5))



IF C13 is 8:30 am. D13 is 5:00 pm. I get 8 hours.
IF C13 is 8:30 am. D13 is 7:00 pm. I get 10 hours.

I need to display 8 and add that to the weekly total for regular hours. I need the "extra" 2 hours to be in another cell - in this instance I13.
I know how to get the 2 hours into I-13 (=SUMI13-E13), but I don't know how to get the E13 to display and add the 8.

BTW, the -.05 takes off for lunch without having to "log out and log in".

Hope that makes sense. It is my first post.

 

[T. Valko]

For overtime hours, I am getting a #VALUE error if there is nothing there. That may be because I added a -.5 to take off for lunch. Otherwise, if they work 8:30 am to 5:00 pm it shows 8 reg hours and .5 OT. It should show 8 reg and 0 OT.

In other words, the clocked time will be 8.5 hours but the actuall work time will only be 8?

OK, try this...

Reg Hrs:

=IF(COUNT(A2:B2)<2,"",MIN(8.5,MOD(B2-A2,1)*24)-0.5)

OT Hrs:

=IF(COUNT(A2:B2)<2,"",MAX(0,MOD(B2-A2,1)*24-8.5))

 

[Lionround]

Thank you T Valko. Problem solved.

 

[T. Valko]

Thank you T Valko. Problem solved.

You're welcome. Thanks for the feedback!