Excel help

[Whiplash]

This is what I need...
I got number (for example) 115,212. Now I want that excel translate it to minutes, seconds and thousandth.
Like this 1:55.212

So, how to make that shit? Maybe nooby question, but I just can't find a way to do it.

[pablinho15]

@Mike;

[Dide Dide]

try this site there is a formula :

http://www.mrexcel.com/forum/showthread.php?t=73152

[Whiplash]

try this site there is a formula :

http://www.mrexcel.com/forum/showthread.php?t=73152

It doesn't work for me.

[Mike Nike]

i could convert that..but i have a german excel version and i dont know the english ones.
basicly it is something like:

1) i = search the index of "," in 115,212
2) sec = get the text left to this index
3) msec = get the text right to this index
4) min = round down to integer (sec/60)
5) sec_new = sec - min*60
6) convertedtext = combine text of min+":"+sec_new+"."+msec

for each of those operations there is a function or a combination of functions (at least in my excelversion)

[Whiplash]

i could convert that..but i have a german excel version and i dont know the english ones.
basicly it is something like:

1) i = search the index of "," in 115,212
2) sec = get the text left to this index
3) msec = get the text right to this index
4) min = round down to integer (sec/60)
5) sec_new = sec - min*60
6) convertedtext = combine text of min+":"+sec_new+"."+msec

for each of those operations there is a function or a combination of functions (at least in my excelversion)

Could you pls just create an example and then send me the excel file.
Put some number to A1 and translate it to B1.
I'm sure you explained it well, but I can't find a way of how to mark text left and right to the index.
Send me that on my email, or upload and write the link here or in pm.
I guess my version of excel will read the file of your.

[Tijny]

i could convert that..but i have a german excel version and i dont know the english ones.
basicly it is something like:

1) i = search the index of "," in 115,212
2) sec = get the text left to this index
3) msec = get the text right to this index
4) min = round down to integer (sec/60)
5) sec_new = sec - min*60
6) convertedtext = combine text of min+":"+sec_new+"."+msec

for each of those operations there is a function or a combination of functions (at least in my excelversion)

Ewwie, why use string operations to parse a number?

@wippe: here's a formula you can use:

Code: Select all

=IF(A1>=60,TEXT(A1/60,"0")&":","")&TEXT(MOD(A1,60),IF(A1>=60,"00","0"))&TEXT(MOD(A1,1),".000")

[Whiplash]

Ewwie, why use string operations to parse a number?

@wippe: here's a formula you can use:

Code: Select all

=IF(A1>=60,TEXT(A1/60,"0")&":","")&TEXT(MOD(A1,60),IF(A1>=60,"00","0"))&TEXT(MOD(A1,1),".000")

It says: "The formula you typed contains an error" bla bla.

Can you use that formula in your excel and then just sent me that file so I can open it in my excel? Just put some number in A1, translate it to B1, save the file and send it to me.

[dede]

It says: "The formula you typed contains an error" bla bla.

If your excel has non-english language, you need to translate those functions to your language (text, if..)

[Pingu]

Just change format of cells, easy :p, hmm all the ones with time are f*** wrong, you could make a custom format

[Whiplash]

It says: "The formula you typed contains an error" bla bla.

If your excel has non-english language, you need to translate those functions to your language (text, if..)

It's english version, but still suck for some reason.

[xzeal]

use custom formating

I use this for my time based analysis on www.gpro.se [hh]"h"mm:ss,000

so basically copy that line into custom formatting

so lets say you got a time of 1 hour 24 minutes and 19 seconds and 091 thousanths and for it to display correctly you have to type

1:24:19,091

and it displays

1h24:19,091

I am sure you can figure out how to edit it yourself for your needs.

not an excel whiz myself took me 10 minutes of trial and error to make that line.

PS: I am using office 2010 and my only trouble is that the source has it displayed as 1h24:19.091 so when I copy it I have to manualy change the "h" to ":" and "." to "," foruntately the find and replace function makes that a 10 second problem.