Excel 2007 : Data Table "total row" won't calculate time

[mclewand]

I'm creating a new timecard system for my employer. The system is a bit complicated since each employee must have a cost code assigned to every 15 minutes worth of work. Our office manager then needs to do a number of things with the cost code data.

To make this process easier for her, I am creating a new timesheet system. Every two weeks the employee's timecards will automatically update the Payroll sheet (see Payroll 4-20 tab). This payroll sheet is a simple excel data table. The problem I'm having is that I can't get the data table to add hours correctly. I've added the Total Row by clicking on "total row" under the design table style options, but the sum is incorrect. Any thoughts you have on how to get this row to correctly calculate time/hours would be greatly appreciated!

[DGagnon]

the 'sum' was actualy doing a count, try this

=SUBTOTAL(9,[Hours])

[mclewand]

the 'sum' was actualy doing a count, try this

=SUBTOTAL(9,[Hours])

Thank you. I tried that but it still doesn't work. It is giving me an answer of 18:15 hours (the total should be 66.25 hours). I also tried =SUBTOTAL(9,[Hours])*24 and the total just comes to 6 hours.

[DGagnon]

make sure you change the format to hours, i am getting the 66 hours 15 Mins

[mclewand]

How do I change the format to hours? On the other tables I have it as custom h:mm but that isn't working here.

[DGagnon]

just right click on the total cell and go to format cell, number, custom and enter [h]:mm

the square brackets will make sure it continues to add hours after 24 instead of restarting.

[mclewand]

It worked!!! Thank you so much for your help!!!

[DGagnon]

no problem at all