| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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),"") |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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),"") |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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),"")) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Strange?! | Excel Discussion (Misc queries) | |||
| Sum Time - strange result (site & cpearson researched) | Excel Worksheet Functions | |||
| strange error | Excel Worksheet Functions | |||
| Very-2 strange | Excel Discussion (Misc queries) | |||
| Something Strange | Setting up and Configuration of Excel | |||
Linear Mode
