0:56 = 56 seconds not 56 minutes

[muster101]

hello all..

have a 4000+ row flat file that has a event duration column for each event, the data is:

MINUTES
0:10 (this is 10 sec)
0:46 (46 sec)
1:26 (1 min 26 sec)
1:52 (1 min 52 sec)
1:48:11 (1 hour 48 min 11 sec)
38:02:00 (etc etc etc)

simply, i need to total this column. problem is, i can't seem to find a way that gives accurate results.

the total should be 39:54:25 but a sum for example returns 20:04:11. excel interprets 10 sec as 10 min.

if i sum
1:48:11
38:02:00

it returns 39:50:11 which is correct.

i did try to search existing posts for solution and while this is probably answered i just couldn't find.

thanks in advance for any help!
This message was edited by muster101 on 2002-11-08 11:01

 

[yosi]

in the sum function use the format
[hh]:mm:ss
instead of
hh:mm:ss

 

[muster101]

yosi..thanks for the response

using as example:
0:09
0:28
0:10
2:36:59

format sum cell [hh]:mm:ss
this still returns 03:23:59 instead of 02:37:46

 

[Whisperer14]

I suspect the reason for your problem is either caused by no formatting of the cells prior to entry. If this was the case when you entered '0:10' XL would have had a best guess and come up with the desired format for that cell as hh:mm - not what you wanted.

If you had entered 0:0:10 then it would have guessed hh:mm:ss and displayed it as 00:00:10 a bit closer to the ideal format of [HH]:mm:ss.

You can check the formatting quite easily by selecting the 0:10 cell and see what format the cell is.

If however you did preformat the cells as hh:mm:ss with or without the [] then XL will attempt to place your entry within that format. An entry this time of 0:10 will still result in a display of 10 minutes - it is imperative that the correct entry sequence is used therefore for 10 seconds the entry should be 0:0:10.

Hope that helps, post back if that is your problem

KR

 

[yosi]

you mix seconds / minutes

00:09:00
00:28:00
00:10:00
02:36:59
03:23:59
total of 03:23:59

00:00:09
00:00:28
00:00:10
02:36:59
02:37:46

total of 02:37:46

 

[Whisperer14]

If that 0:10 cell was formatted as hh:mm then the following code will correct the entries and the format - save a bit of typing.

You will need to change the start/finish of the iRow loop and the column number (1) to cover your worksheet. It is tested but do make sure that you have a copy just in case.

Place the code in the VBA element of your spreadsheet and select run from the menu.

Best wishes

Sub ChangeFormat()
Dim iRow As Integer

For iRow = 1 To 6
If Cells(iRow, 1).NumberFormat = "h:mm" Then
Cells(iRow, 1) = Cells(iRow, 1) / 60
Cells(iRow, 1).NumberFormat = "[hh]:mm:ss"
End If
Next iRow
End Sub
This message was edited by Whisperer14 on 2002-11-08 13:07

 

[muster101]

whisper.....brilliant! works perfectly and much appreciate.

far superior to what i had ended up doing to solve this..

as you previously suggested, i imported this column (A) as text..
then in col B:

=IF(LEN(A2)=4,"00:0"&A2,IF(LEN(A2)=5,"00:"&A2,IF(LEN(A2)=7,"0"&A2,A2)))

then, col C:
value(b2) and format col as hh:mm:ss

thanks again!!