Excel 2007 : Calculate total hours worked

**elexiahope** · 01-22-2011, 12:26 PM

Using top columns showing category of cells below: A1: Date: B1: Time in: C1: time out D1 hours worked. A2: 1/4/2011 B2:formula is =TIME(8,3,0) showing as 8:03 in cell. C2 formula is =TIME(15,5,0), again showing as 15:05 in cell. D2 formula is =IF(C2<B2,C2+1,C2)-B2, showing as 7:02 hours, total worked that day, in cell. I used the same formulas up for row 6, one for each day of the week. How do I total the hours worked for the week? And then adding another week, using the same formulas, how do I total the hours worked biweekly?

**daddylonglegs** · 01-22-2011, 01:16 PM

You can just sum the daily totals, e.g. for the first week

=SUM(D2:D6)

just change the range to sum 2 weeks

In the cell with that formula make sure you custom format as [h]:mm with square brackets, otherwise totals of 24:00 hours and over don't display correctly

**elexiahope** · 01-23-2011, 03:59 PM

Originally Posted by daddylonglegs

You can just sum the daily totals, e.g. for the first week

=SUM(D2:D6)

just change the range to sum 2 weeks

In the cell with that formula make sure you custom format as [h]:mm with square brackets, otherwise totals of 24:00 hours and over don't display correctly

Here is my table:
Date: Time in: Time out: Hours worked:
1/16/11 7:03 16:33 9:30
1/17/11 8:00 16:09 8:09
1/20/11 8:01 16:01 8:00
1/21/11 8:02 14:57 6:55
1/22/11 7:02 16:04 9:02
Total hours: 17:36
I used the formula =SUM(D3:D7), but the total hours is 17:36, which it's suppose to be 41.36. Can you please tell me a formula that will so the total hours correctly?

**Cutter** · 01-23-2011, 04:18 PM

Notice that the result you got is exactly 24 hrs less than what you expected?

Did you format the cell like DLL suggested in Post #2?

Following his advice will give you the correct result.