It is currently Tue Nov 19, 2019 2:34 am


Excel help

Discussion related to Turbo Sliders and beta version feedback.

Moderator: Forum Moderators

  • Author
  • Message
Offline
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

PostThu 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.
Offline
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

PostThu Sep 22, 2011 2:09 pm

@Mike;
Offline
User avatar

Dide Dide

Community User Level: 2

Community User Level: 2

  • Posts: 230
  • Joined: Tue Jul 06, 2010 8:56 am
  • Location: Mozambique, Maputo

PostThu Sep 22, 2011 5:40 pm

try this site there is a formula :

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

Whiplash

Community User Level: 5

Community User Level: 5

  • Posts: 2325
  • Joined: Sun Apr 19, 2009 4:57 pm
  • Location: Bosnia

PostThu 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:
Offline
User avatar

Mike Nike

Community User Level: 5

Community User Level: 5

  • Posts: 1131
  • Joined: Sat Oct 30, 2004 3:11 pm
  • Location: Germany

PostFri 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)
Offline
User avatar

Whiplash

Community User Level: 5

Community User Level: 5

  • Posts: 2325
  • Joined: Sun Apr 19, 2009 4:57 pm
  • Location: Bosnia

PostFri 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.
Offline
User avatar

Tijny

Community User Level: 5

Community User Level: 5

  • Posts: 1514
  • Joined: Sat Nov 06, 2004 7:59 pm
  • Location: Netherlands

PostFri 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")
Offline
User avatar

Whiplash

Community User Level: 5

Community User Level: 5

  • Posts: 2325
  • Joined: Sun Apr 19, 2009 4:57 pm
  • Location: Bosnia

PostFri 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.
Offline
User avatar

dede

Community User Level: 5

Community User Level: 5

  • Posts: 3314
  • Joined: Mon Apr 10, 2006 10:29 am
  • Location: Milan (Italy)

PostFri 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..)
Offline
User avatar

Pingu

Community User Level: 5

Community User Level: 5

  • Posts: 1191
  • Joined: Sun Jul 12, 2009 4:25 pm
  • Location: NETHERLANDS

PostFri 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
Offline
User avatar

Whiplash

Community User Level: 5

Community User Level: 5

  • Posts: 2325
  • Joined: Sun Apr 19, 2009 4:57 pm
  • Location: Bosnia

PostFri 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.
Offline
User avatar

xzeal

Community User Level: 3

Community User Level: 3

  • Posts: 445
  • Joined: Sat Oct 30, 2004 10:32 am
  • Location: Tallinn, Estonia

PostFri 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.

Return to General Discussion

Who is online

Users browsing this forum: No registered users and 0 guests

cron