Need help totaling hours on a time sheet

[BluesJunkie]

I have created a time sheet for employees (Attached) but the sum of the hours starts over after exceeding 24 hours. How can correct this formula?

Column B is time in and column C is time out. Column D is daily hours worked "D2 =C2-B2". These columns are formatted for time in 24hr format.

The formula I am using for my weekly total is D11 =Sum(D2:D8).

I would also like to set this up so that each biweekly pay period totals regular hours ending at 40 (23)and a separate cell (D24) displays OT hours.

[Whizbang]

Change the formatting. Something like the format [h]:mm:ss;@ should do the trick.

D23 = Min(80, D11+D21)
D24 = Max(0, D11+D21-80)

[BluesJunkie]

Thanks for your help. Changing the format did the trick for totaling the hours, but I realized I needed to address the OT differently. If I use the formula you sent for the OT, an employee could work over 40 hrs one week and less that 40 the second week, totaling less that 80 hrs for the pay period, and they would show no overtime. So I set up the OT to be calculated weekly, but am missing something in the formulas that I tried to adapt from what you sent. Please check the cells for "Weekly Hrs Total" and "Weekly Hrs OT" (D11 and D12) in this updated attachment.

[Whizbang]

Try this instead.

D23 =Min(40, D11)+Min(40,D21)
D24 =Max(0, D11-40)+Max(0,D21-40)

[popipipo]

40 means 40 days , you want hours:

=MIN("40:00";SUM(D3:D9))

Try this one

[BluesJunkie]

Thank you so much for your help. That was what i needed.