strange time value
 

hello,
in a column I have this value kind, that is an time kind:


ORIGINAL BUT I WISH HAVE
6 6.00
1103 11.03
213509 21.35.09
23823 2.38.23
162657 16.26.57
214518 21.45.18
129 1.29
41942 4.19.42

The values are many thousands.
have u got any solution?
thanks for your help
--
BBB

strange time value
 

Try:

=IF(A1<24,TIME(A1,0,0),IF(A1<2400,TIME(INT(A1/100),MOD(A1,100),0),TIME(INT(A1/10000),INT(MOD(A1,10000)/100),MOD(A1,100))))

Regards,
Fred

"andrew" wrote in message
...
hello,
in a column I have this value kind, that is an time kind:


ORIGINAL BUT I WISH HAVE
6 6.00
1103 11.03
213509 21.35.09
23823 2.38.23
162657 16.26.57
214518 21.45.18
129 1.29
41942 4.19.42

The values are many thousands.
have u got any solution?
thanks for your help
--
BBB

strange time value
 

andrew wrote:
hello,
in a column I have this value kind, that is an time kind:


ORIGINAL BUT I WISH HAVE
6 6.00
1103 11.03
213509 21.35.09
23823 2.38.23
162657 16.26.57
214518 21.45.18
129 1.29
41942 4.19.42

The values are many thousands.
have u got any solution?
thanks for your help


Try this:

=--(LEFT(IF(ISODD(LEN(A1)),"0","")&A1&"0000",2))&
"."&MID(IF(ISODD(LEN(A1)),"0","")&A1&"0000",3, 2)&
IF(LEN(A1)4,"."&MID(IF(ISODD(LEN(A1)),"0","")&A1& "0000",5,2),"")

strange time value
 

fred, glen,

thanks a lot.
--
BBB


"Glenn" wrote:

andrew wrote:
hello,
in a column I have this value kind, that is an time kind:


ORIGINAL BUT I WISH HAVE
6 6.00
1103 11.03
213509 21.35.09
23823 2.38.23
162657 16.26.57
214518 21.45.18
129 1.29
41942 4.19.42

The values are many thousands.
have u got any solution?
thanks for your help


Try this:

=--(LEFT(IF(ISODD(LEN(A1)),"0","")&A1&"0000",2))&
"."&MID(IF(ISODD(LEN(A1)),"0","")&A1&"0000",3, 2)&
IF(LEN(A1)4,"."&MID(IF(ISODD(LEN(A1)),"0","")&A1& "0000",5,2),"")

strange time value
 

Glenn wrote:
andrew wrote:
hello,
in a column I have this value kind, that is an time kind:


ORIGINAL BUT I WISH HAVE
6 6.00
1103 11.03
213509 21.35.09
23823 2.38.23
162657 16.26.57
214518 21.45.18
129 1.29
41942 4.19.42

The values are many thousands.
have u got any solution? thanks for your help


Try this:

=--(LEFT(IF(ISODD(LEN(A1)),"0","")&A1&"0000",2))&
"."&MID(IF(ISODD(LEN(A1)),"0","")&A1&"0000",3, 2)&
IF(LEN(A1)4,"."&MID(IF(ISODD(LEN(A1)),"0","")&A1& "0000",5,2),"")


Actually, all of the "0000"'s can be reduced to "00".

And if you actually want time values as a result:

=--(--(LEFT(IF(ISODD(LEN(A1)),"0","")&A1&"00",2))&
":"&MID(IF(ISODD(LEN(A1)),"0","")&A1&"00",3,2) &
IF(LEN(A1)4,":"&MID(IF(ISODD(LEN(A1)),"0","")&A1& "00",5,2),""))

strange time value
 

On Tue, 3 Feb 2009 11:40:05 -0800, andrew
wrote:

hello,
in a column I have this value kind, that is an time kind:


ORIGINAL BUT I WISH HAVE
6 6.00
1103 11.03
213509 21.35.09
23823 2.38.23
162657 16.26.57
214518 21.45.18
129 1.29
41942 4.19.42

The values are many thousands.
have u got any solution?
thanks for your help

If you are OK with the results being text strings, then:

=TEXT(A1,"[9999]##\.##\.##;[99]#\.00;#.00")

However, if you want to retain the values as numeric, you can use the same
format argument as a "custom format"
--ron