Timesheet calculation of hours

patricka

New Member
Joined
Sep 28, 2006
Messages
2
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Your formula is OK but you should custom format the cell that contains it to

[h]:mm
 

Forum statistics

Threads
1,136,592
Messages
5,676,690
Members
419,644
Latest member
KeelsM

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top