hello,

I write time in [hh:mm:ss,000] custom format and
it works fine (for some car racing statistics)
and in the end I have to sort the list ascending
to see what car was the quickest one.
The problem is that I have to sort the score,
not the time.. and the score is a number that I
read from time, for example:

00:00:54,121 would be 54121
00:20:45,215 would be 2045215

and so on..

I don't have an idea how to get that number from
this format. Please help!

Ivan

What's the 3-digit number on the end? Is this the score?

He is working in Minutes, Seconds and Thousandths of seconds. At the end of
the day, he wants to concatenate the minutes - say 20, the seconds - say 12,
and the thousandths, say 254, to then get 2012254. He does not want to
change the times in any way, but to get a numeric value based on the
concatenation.

"Piers 2k" wrote:

What's the 3-digit number on the end? Is this the score?

?B?a2Fzc2ll?= wrote in
:

He is working in Minutes, Seconds and Thousandths of seconds. At the
end of the day, he wants to concatenate the minutes - say 20, the
seconds - say 12, and the thousandths, say 254, to then get 2012254.
He does not want to change the times in any way, but to get a numeric
value based on the concatenation.

exactly

--

Bllich

Try this formula
=CONCATENATE(INT(B1*24),INT((B1*24-INT(B1*24))*60),ROUND((B1*24*60-INT(B1*24*60))*60*1000,0))
Assuming that the time is in B1

"Piers 2k" wrote:

What's the 3-digit number on the end? Is this the score?

Ivan,
Something like:
MsgBox Replace(Range("A2").Text, ":", "") * 1000

NickHK

"Ivan" .88...
hello,

I write time in [hh:mm:ss,000] custom format and
it works fine (for some car racing statistics)
and in the end I have to sort the list ascending
to see what car was the quickest one.
The problem is that I have to sort the score,
not the time.. and the score is a number that I
read from time, for example:

00:00:54,121 would be 54121
00:20:45,215 would be 2045215

and so on..

I don't have an idea how to get that number from
this format. Please help!

Ivan

"NickHK" wrote in news:OVgwLQV6GHA.1188
@TK2MSFTNGP05.phx.gbl:

Ivan,
Something like:
MsgBox Replace(Range("A2").Text, ":", "") * 1000

NickHK

hey Nick, I get your idea but I know nothing about
showing MsgBox from Excel, or macro's .. :(
I have like - field A1 where my 00:00:54,121 time is
and I need to get nubmer 54121 written in A2
from this A1 field.. (so A2 depends on A1)
if you could just say what I need to type in A2 field,
a formula or expression ;)


Ivan (Bllich)

Ivan,
Alt+F11, right-click the workbook tree, add a module.
Paste in

Public Function MakeScore(argInput As Variant) As Long
MakeScore = Replace(argInput.Text, ":", "") * 1000
End Function

Then in the worksheet, type :
=MakeScore("A1")
or the cell of interest

NickHK

"Ivan" .88...
"NickHK" wrote in news:OVgwLQV6GHA.1188
@TK2MSFTNGP05.phx.gbl:

Ivan,
Something like:
MsgBox Replace(Range("A2").Text, ":", "") * 1000

NickHK

hey Nick, I get your idea but I know nothing about
showing MsgBox from Excel, or macro's .. :(
I have like - field A1 where my 00:00:54,121 time is
and I need to get nubmer 54121 written in A2
from this A1 field.. (so A2 depends on A1)
if you could just say what I need to type in A2 field,
a formula or expression ;)


Ivan (Bllich)

Ivan wrote:
hello,

I write time in [hh:mm:ss,000] custom format and
it works fine (for some car racing statistics)
and in the end I have to sort the list ascending
to see what car was the quickest one.
The problem is that I have to sort the score,
not the time.. and the score is a number that I
read from time, for example:

00:00:54,121 would be 54121
00:20:45,215 would be 2045215

and so on..

I don't have an idea how to get that number from
this format. Please help!

Ivan

Does this work? Assume that D1 holds the time:

=VALUE(CONCATENATE(HOUR(D1),REPT("0",2 -
LEN(MINUTE(D1))),MINUTE(D1),REPT("0",2 -
LEN(SECOND(D1))),,SECOND(D1),REPT("0",3-LEN(FIXED(24*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0))),FIXED(2 4*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0)))

Hope that helps

-John Coleman

"John Coleman" wrote in
ups.com:


Does this work? Assume that D1 holds the time:

=VALUE(CONCATENATE(HOUR(D1),REPT("0",2 -
LEN(MINUTE(D1))),MINUTE(D1),REPT("0",2 -
LEN(SECOND(D1))),,SECOND(D1),REPT("0",3-LEN(FIXED(24*3600000*(D1-TIME(H
OUR(D1),MINUTE(D1),SECOND(D1))),0))),FIXED(24*3600 000*(D1-TIME(HOUR(D1)
,MINUTE(D1),SECOND(D1))),0)))

Hope that helps

-John Coleman

ok, my field is AB3 instead of D1 but still when I apply it on your
formula

=VALUE(CONCATENATE(HOUR(AB3),REPT("0",2 -LEN(MINUTE(AB3))),MINUTE
(AB3),REPT("0",2 -LEN(SECOND(AB3))),,SECOND(AB3),REPT("0",3-LEN(FIXED(24*
3600000*(AB3-TIME(HOUR(AB3),MINUTE(AB3),SECOND(AB3))),0))),FIXE D(24*
3600000*(AB3-TIME(HOUR(AB3),MINUTE(AB3),SECOND(AB3))),0)))

is says - formula error and it show to REPT

--

Bllich

Bllich wrote:
"John Coleman" wrote in
ups.com:


Does this work? Assume that D1 holds the time:

=VALUE(CONCATENATE(HOUR(D1),REPT("0",2 -
LEN(MINUTE(D1))),MINUTE(D1),REPT("0",2 -
LEN(SECOND(D1))),,SECOND(D1),REPT("0",3-LEN(FIXED(24*3600000*(D1-TIME(H
OUR(D1),MINUTE(D1),SECOND(D1))),0))),FIXED(24*3600 000*(D1-TIME(HOUR(D1)
,MINUTE(D1),SECOND(D1))),0)))

Hope that helps

-John Coleman

ok, my field is AB3 instead of D1 but still when I apply it on your
formula

=VALUE(CONCATENATE(HOUR(AB3),REPT("0",2 -LEN(MINUTE(AB3))),MINUTE
(AB3),REPT("0",2 -LEN(SECOND(AB3))),,SECOND(AB3),REPT("0",3-LEN(FIXED(24*
3600000*(AB3-TIME(HOUR(AB3),MINUTE(AB3),SECOND(AB3))),0))),FIXE D(24*
3600000*(AB3-TIME(HOUR(AB3),MINUTE(AB3),SECOND(AB3))),0)))

is says - formula error and it show to REPT

--

Bllich

After I removed Google/clipboard induced line breaks I was able to get
your AB3 version to work. These long formulas seem hard to communicate
over the web. I had some trouble with Alok's formula. I wonder how they
deal with this in the Excel worksheet function newsgroup.

-John Coleman

John Coleman wrote:
Ivan wrote:
hello,

I write time in [hh:mm:ss,000] custom format and
it works fine (for some car racing statistics)
and in the end I have to sort the list ascending
to see what car was the quickest one.
The problem is that I have to sort the score,
not the time.. and the score is a number that I
read from time, for example:

00:00:54,121 would be 54121
00:20:45,215 would be 2045215

and so on..

I don't have an idea how to get that number from
this format. Please help!

Ivan

Does this work? Assume that D1 holds the time:

=VALUE(CONCATENATE(HOUR(D1),REPT("0",2 -
LEN(MINUTE(D1))),MINUTE(D1),REPT("0",2 -
LEN(SECOND(D1))),,SECOND(D1),REPT("0",3-LEN(FIXED(24*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0))),FIXED(2 4*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0)))

Hope that helps

-John Coleman

A small, mostly aesthetic, correction:

=VALUE(CONCATENATE(HOUR(D1),REPT("0",2 -
LEN(MINUTE(D1))),MINUTE(D1),REPT("0",2 -
LEN(SECOND(D1))),SECOND(D1),REPT("0",3-LEN(FIXED(24*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0))),FIXED(2 4*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0)))

(a double comma was replaced by a single comma - I unknowingly was
concatenating an empty string)

More importantly - I was curious to see how Excel formula bar -
clipboard - Google Group - clipboard -Excel formula bar translations
would go - and the result seems somewhat unpredictable. For example -
some of the D1s were inexplicably translated to -D1 and unwanted line
breaks were introduced. If you have similar trouble just remove the
line breaks, etc. The point of all the Rept functions was so that 12:05
maps to 1205 and not 125, etc.

Hope that helps

-John Coleman

Excellently done John. Got to the same solution, but not as quick as you!!!!

"John Coleman" wrote:


John Coleman wrote:
Ivan wrote:
hello,

I write time in [hh:mm:ss,000] custom format and
it works fine (for some car racing statistics)
and in the end I have to sort the list ascending
to see what car was the quickest one.
The problem is that I have to sort the score,
not the time.. and the score is a number that I
read from time, for example:

00:00:54,121 would be 54121
00:20:45,215 would be 2045215

and so on..

I don't have an idea how to get that number from
this format. Please help!

Ivan

Does this work? Assume that D1 holds the time:

=VALUE(CONCATENATE(HOUR(D1),REPT("0",2 -
LEN(MINUTE(D1))),MINUTE(D1),REPT("0",2 -
LEN(SECOND(D1))),,SECOND(D1),REPT("0",3-LEN(FIXED(24*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0))),FIXED(2 4*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0)))

Hope that helps

-John Coleman

A small, mostly aesthetic, correction:

=VALUE(CONCATENATE(HOUR(D1),REPT("0",2 -
LEN(MINUTE(D1))),MINUTE(D1),REPT("0",2 -
LEN(SECOND(D1))),SECOND(D1),REPT("0",3-LEN(FIXED(24*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0))),FIXED(2 4*3600000*(D1-TIME(HOUR(D1),MINUTE(D1),SECOND(D1))),0)))

(a double comma was replaced by a single comma - I unknowingly was
concatenating an empty string)

More importantly - I was curious to see how Excel formula bar -
clipboard - Google Group - clipboard -Excel formula bar translations
would go - and the result seems somewhat unpredictable. For example -
some of the D1s were inexplicably translated to -D1 and unwanted line
breaks were introduced. If you have similar trouble just remove the
line breaks, etc. The point of all the Rept functions was so that 12:05
maps to 1205 and not 125, etc.

Hope that helps

-John Coleman

thanks for all the trouble, I don't want to bug you anymore,
though it didn't solve the problem because of the field
format that I use..I think.

(If someone is still willing to help):

I tried with module, but I think that input type in the function
is expected as a string, not as hh:mm:ss,000
and John's formula just says error, when I changed the formula's field
type to TEXT I got ########## :)

U have uploaded this file
http://www.pmfst.hr/~ivabal/Slalom_2.xls

don't let Croatian words confuse you ;)
just look at AB3 and try to give me a score number in AC3

my mail is


Ivan

=SUBSTITUTE(TEXT(AB3,"hhmmss.000"),".","")*1

You may have to adjust the "." for your regional settings.

--
Regards,
Tom Ogilvy



"Ivan" wrote:

hello,

I write time in [hh:mm:ss,000] custom format and
it works fine (for some car racing statistics)
and in the end I have to sort the list ascending
to see what car was the quickest one.
The problem is that I have to sort the score,
not the time.. and the score is a number that I
read from time, for example:

00:00:54,121 would be 54121
00:20:45,215 would be 2045215

and so on..

I don't have an idea how to get that number from
this format. Please help!

Ivan

Elegant, as usual!!

Tom Ogilvy wrote:
=SUBSTITUTE(TEXT(AB3,"hhmmss.000"),".","")*1

You may have to adjust the "." for your regional settings.

--
Regards,
Tom Ogilvy



"Ivan" wrote:

hello,

I write time in [hh:mm:ss,000] custom format and
it works fine (for some car racing statistics)
and in the end I have to sort the list ascending
to see what car was the quickest one.
The problem is that I have to sort the score,
not the time.. and the score is a number that I
read from time, for example:

00:00:54,121 would be 54121
00:20:45,215 would be 2045215

and so on..

I don't have an idea how to get that number from
this format. Please help!

Ivan

Tom,
Duh. I always forget the TEXT function in Excel and assume it works with
values.

NickHK

"Tom Ogilvy" ...
=SUBSTITUTE(TEXT(AB3,"hhmmss.000"),".","")*1

You may have to adjust the "." for your regional settings.

--
Regards,
Tom Ogilvy



"Ivan" wrote:

hello,

I write time in [hh:mm:ss,000] custom format and
it works fine (for some car racing statistics)
and in the end I have to sort the list ascending
to see what car was the quickest one.
The problem is that I have to sort the score,
not the time.. and the score is a number that I
read from time, for example:

00:00:54,121 would be 54121
00:20:45,215 would be 2045215

and so on..

I don't have an idea how to get that number from
this format. Please help!

Ivan

Bllich

thanks for all the trouble, I don't want to bug you anymore,
though it didn't solve the problem because of the field
format that I use..I think.

(If someone is still willing to help):

I tried with module, but I think that input type in the function
is expected as a string, not as hh:mm:ss,000
and John's formula just says error, when I changed the formula's field
type to TEXT I got ########## :)

U have uploaded this file
http://www.pmfst.hr/~ivabal/Slalom_2.xls

don't let Croatian words confuse you ;)
just look at AB3 and try to give me a score number in AC3

my mail is


Ivan

I formatted the cells AC3 as General. Pasted in my formula and copied it
down the column. It immediately returned these scores:

44908 <=== AC3
28908
12908
32908
28908
10908
50908
8908
28908
10908
30908
28908
8908
48908
12908
28908
8908
28908
30908
12908
52908
10908
30908
12908
32908
28908
10908
50908
8908
28908
10908
30908
28908
8908
48908
12908
28908
8908
28908
30908
12908
52908
10908
30908
12908
32908
28908
10908
50908
8908
28908
10908
30908
28908
8908
48908

I stopped there. why get drunk on success.



--
Regards,
Tom Ogilvy
"Bllich" wrote in message
...
thanks for all the trouble, I don't want to bug you anymore,
though it didn't solve the problem because of the field
format that I use..I think.

(If someone is still willing to help):

I tried with module, but I think that input type in the function
is expected as a string, not as hh:mm:ss,000
and John's formula just says error, when I changed the formula's field
type to TEXT I got ########## :)

U have uploaded this file
http://www.pmfst.hr/~ivabal/Slalom_2.xls

don't let Croatian words confuse you ;)
just look at AB3 and try to give me a score number in AC3

my mail is


Ivan

"Tom Ogilvy" wrote in
:

I formatted the cells AC3 as General. Pasted in my formula and copied
it down the column. It immediately returned these scores:

44908 <=== AC3
28908
12908
32908
28908
10908
50908
8908
28908

yes yes .. I was twisting your formula and got the same also.. ;)

I posted again "time to integer2" article on groups later on
and Sandy instructed me to check it out again and I did!

though I had to divide whole formula by 1 to get rid of the zeros
and i needed to change commas to ;

it works, thanks man

--

Bllich

maybe you didn't copy the whole formula. The *1 on the end (as written)
would remove the zeros.

=SUBSTITUTE(TEXT(AB3,"hhmmss.000"),".","")*1

as to the semicolon:

You may have to adjust the "." for your regional settings.

perhaps wasn't complete in stating that you might have to adjust the commas
in the formula as well.

--
Regards,
Tom Ogilvy




"Bllich" wrote in message
...
"Tom Ogilvy" wrote in
:

I formatted the cells AC3 as General. Pasted in my formula and copied
it down the column. It immediately returned these scores:

44908 <=== AC3
28908
12908
32908
28908
10908
50908
8908
28908

yes yes .. I was twisting your formula and got the same also.. ;)

I posted again "time to integer2" article on groups later on
and Sandy instructed me to check it out again and I did!

though I had to divide whole formula by 1 to get rid of the zeros
and i needed to change commas to ;

it works, thanks man

--

Bllich