Excel help
Moderator: Forum Moderators
Excel help
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.
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
 Community User Level: 2
 Posts: 261
 Joined: Sat Nov 15, 2008 8:41 pm
 Location: Tigre,Buenos Aires,Argentina
It doesn't work for me.Dide Dide wrote:try this site there is a formula :
http://www.mrexcel.com/forum/showthread.php?t=73152
 Mike Nike
 Community User Level: 5
 Posts: 1131
 Joined: Sat Oct 30, 2004 3:11 pm
 Location: Germany
 Contact:
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)
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.Mike Nike wrote: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)
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.
Ewwie, why use string operations to parse a number?Mike Nike wrote: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)
@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.Tijny wrote: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")
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.

 Community User Level: 3
 Posts: 445
 Joined: Sat Oct 30, 2004 10:32 am
 Location: Tallinn, Estonia
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.
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.