strange time value

  • Thread starter Thread starter andrew
  • Start date Start date
A

andrew

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:

=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 said:
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
Click to expand...


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),"")
 
fred, glen,

thanks a lot.
--
BBB


Glenn said:
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),"")
Click to expand...
 
Glenn said:
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),"")
Click to expand...


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),""))
 
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
Click to expand...

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
 
Back
Top