# Timesheet calculation of hours

#### patricka

##### New Member
I have been woring on automating a timesheet and have run into a problem on the final step of adding up the hours per day to return a total hours worked per 2 week period. The sheet is set up as follows;

A10=AM Time In (h:mm AM/PM format)
A11=AM Time Out (h:mm AM/PM format)
A12=PM Time In (h:mm AM/PM format)
A13=PM Time Out (h:mm AM/PM format)
A14=Total Hours Worked (h:mm format)
Q14= Sum (b14:q14) (h:mm format)

The calculation formula is a simple sum(b14:q14) formula and it works great, until the total hours summed becomes greater than 24. At that point the total returns to 0 and goes from there. For example, if the total hours worked is 23, no problem, 23 is returned as the sum. However, if the total hours worked is 28, the formula returns 4. When I highlight the entire row to be summed, the quick sum feature returns the correct sum, but I can't get the formula to work properly. Any ideas? Thanks in advance.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
Your formula is OK but you should custom format the cell that contains it to

[h]:mm

That was quick and easy, THANK YOU!

Replies
8
Views
293
Replies
10
Views
422
Replies
15
Views
712
Replies
8
Views
424
Replies
2
Views
510

1,220,010
Messages
6,151,443
Members
451,028
Latest member
greekness1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back