SUBTOTAL function does not produce correct results when using time in hh:mm:ss

[Rocky.Paredes]

Hello friends - When I do a subtotal to add hh:mm:ss I get the wrong result but when I use the =sum() function it yields the correct result. The reason I would like to use subtotal is so that I do not have to manually perform the addition - could you help please. Example below.

I use Excel 2010; cells are formatted custom h:mm:ss

Agent Name Logon Date/Time Logout Date/Time Calculations using sum formula

Employee1 12/1/2012 7:01 12/1/2012 7:01 0:00:47

Employee1 12/1/2012 7:03 12/1/2012 11:55 4:52:14

Employee1 12/3/2012 7:00 12/3/2012 15:01 8:01:04

Employee1 12/3/2012 15:05 12/3/2012 17:42 2:36:40

Employee1 12/4/2012 7:00 12/4/2012 17:42 10:41:47 26:12:32

Employee1 Total 2:12:32

Employee2 12/2/2012 7:32 12/2/2012 7:32 0:00:06

Employee2 12/2/2012 7:35 12/2/2012 7:44 0:08:50

Employee2 12/3/2012 6:07 12/3/2012 11:25 5:17:58

Employee2 12/4/2012 6:02 12/4/2012 11:25 5:23:39

Employee2 12/5/2012 6:03 12/5/2012 11:26 5:23:22

Employee2 12/2/2012 7:54 12/2/2012 15:27 7:32:07 23:46:02

Employee2 Total 23:46:02

Grand Total 1:58:34

 

[NARAYANK991]

Hi Rocky ,


Can you specify which formula does not work correctly if you use the SUBTOTAL function ? SUBTOTAL(9,....) works correctly.


Narayan

 

[Rocky.Paredes]

Hi Narayan it is the subtotal: 0:00:47 + 4:52:14 + 8:01:04 + 2:36:40 + 10:41:47 = 26:12:32 - those numbers range from Cell D2 through D6. But the subtotal formula =subtotal(9,D2:D6) = 2:12:32 incorrect result. Thank you, Narayan I appreciate your time.

 

[Hui]

Rocky


Firstly, Welcome top the Chandoo.org Forums


For Hours greater than 12 change the display format of the Times to: [h]:mm:ss


Select the Column

Ctrl 1

on the Number Tab, select Custom

Enter [h]:mm:ss in the Type: box

 

[Rocky.Paredes]

Hi Hui - thank you for the warm welcome. I'm really excited about being part of the chandoo community. . My spreadsheet is already formatted in that way. Somehow the subtotal function using sum does not yeild the correct results. I also tried the same data on Excel 2007 - same result. Thank you.

 

[NARAYANK991]

Hi Rocky ,


On my computer , SUBTOTAL(9,D2:D6) where D2:D6 contain the following data :

[pre]

0:00:47
4:52:14
8:01:04
2:36:40
10:41:47

[/pre]
gives the same result as SUM.


The result that you have got 2:12:32 is possible if the cell has been formatted for h:mm:ss. Any time the number of hours exceeds 24 , this will be subtracted for this particular format ; as Hui has pointed out , [h]:mm:ss will ensure that this does not happen , and you will correctly get 26:12:32


Narayan

 

[Rocky.Paredes]

Ok - I understand now - Hui I apologize I just didn't see that option [h]:mm:ss.And thank you Narayan for clarifying this for me. It works! Thanks alot - I really appreciate the help.