Excel help

Discussion related to Turbo Sliders and beta version feedback.

Moderator: Forum Moderators

Post Reply
User avatar
Whiplash
Community User Level: 5
Community User Level: 5
Posts: 2325
Joined: Sun Apr 19, 2009 4:57 pm
Location: Bosnia

Excel help

Post by Whiplash » Thu Sep 22, 2011 12:48 pm

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.

User avatar
pablinho15
Community User Level: 2
Community User Level: 2
Posts: 261
Joined: Sat Nov 15, 2008 8:41 pm
Location: Tigre,Buenos Aires,Argentina

Post by pablinho15 » Thu Sep 22, 2011 2:09 pm

@Mike;

Dide Dide
Community User Level: 2
Community User Level: 2
Posts: 230
Joined: Tue Jul 06, 2010 8:56 am
Location: Mozambique, Maputo

Post by Dide Dide » Thu Sep 22, 2011 5:40 pm

try this site there is a formula :

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

User avatar
Whiplash
Community User Level: 5
Community User Level: 5
Posts: 2325
Joined: Sun Apr 19, 2009 4:57 pm
Location: Bosnia

Post by Whiplash » Thu Sep 22, 2011 6:28 pm

Dide Dide wrote:try this site there is a formula :

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

It doesn't work for me. :evil:

User avatar
Mike Nike
Community User Level: 5
Community User Level: 5
Posts: 1131
Joined: Sat Oct 30, 2004 3:11 pm
Location: Germany
Contact:

Post by Mike Nike » Fri Sep 23, 2011 1:18 am

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)

User avatar
Whiplash
Community User Level: 5
Community User Level: 5
Posts: 2325
Joined: Sun Apr 19, 2009 4:57 pm
Location: Bosnia

Post by Whiplash » Fri Sep 23, 2011 12:18 pm

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)


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
Community User Level: 5
Community User Level: 5
Posts: 1514
Joined: Sat Nov 06, 2004 7:59 pm
Location: Netherlands

Post by Tijny » Fri Sep 23, 2011 1:02 pm

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)
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")

User avatar
Whiplash
Community User Level: 5
Community User Level: 5
Posts: 2325
Joined: Sun Apr 19, 2009 4:57 pm
Location: Bosnia

Post by Whiplash » Fri Sep 23, 2011 2:21 pm

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")

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

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.

User avatar
dede
Community User Level: 5
Community User Level: 5
Posts: 3314
Joined: Mon Apr 10, 2006 10:29 am
Location: Milan (Italy)
Contact:

Post by dede » Fri Sep 23, 2011 5:28 pm

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

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

User avatar
Pingu
Community User Level: 5
Community User Level: 5
Posts: 1191
Joined: Sun Jul 12, 2009 4:25 pm
Location: NETHERLANDS

Post by Pingu » Fri Sep 23, 2011 8:38 pm

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

User avatar
Whiplash
Community User Level: 5
Community User Level: 5
Posts: 2325
Joined: Sun Apr 19, 2009 4:57 pm
Location: Bosnia

Post by Whiplash » Fri Sep 23, 2011 9:59 pm

dede wrote:
Whiplash wrote:It says: "The formula you typed contains an error" bla bla. :evil:

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

Post by xzeal » Fri Sep 23, 2011 11:02 pm

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.

Post Reply