0:56 = 56 seconds not 56 minutes

muster101

New Member
Joined
Mar 14, 2002
Messages
13
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

yosi

Board Regular
Joined
May 14, 2002
Messages
67
in the sum function use the format
[hh]:mm:ss
instead of
hh:mm:ss
 

muster101

New Member
Joined
Mar 14, 2002
Messages
13
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

Well-known Member
Joined
Nov 6, 2002
Messages
589
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

Board Regular
Joined
May 14, 2002
Messages
67

ADVERTISEMENT

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

Well-known Member
Joined
Nov 6, 2002
Messages
589
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

New Member
Joined
Mar 14, 2002
Messages
13
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!!
 

Forum statistics

Threads
1,143,708
Messages
5,720,388
Members
422,282
Latest member
psunith

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