|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hi,
Hope someone can help me with this. I am trying to convert "Wed. Sep. 20,
2006 14:16" into a format recognized by excel (9/30/06 14:16). Have tried to
extract and collate the data using
"=CONCATENATE(VALUE(RIGHT(LEFT(B3,12),2))," ",RIGHT(LEFT(B3,8),3),"
",RIGHT(LEFT(B3,18),4)," ",RIGHT(B3,5))", but all my efforts are in vain when
I try to convert it to a date and time format recognized by excel. The lenght
of the text that I'm trying to convert is exactly 24 characters.
Hope someone can help me with this.
Thanks
Arun
|
|
Hi Arun,
One way is to obtain the date say in c3
=CONCATENATE(VALUE(RIGHT(LEFT(b3,12),2))," ",RIGHT(LEFT(b3,8),3),"
",RIGHT(LEFT(b3,18),4)) giving 20 Sep 2006
then the time say in d3
=RIGHT(b3,5) giving 14:16
The date can be converted to a serial number say in e3
= datevalue(c3) giving 09/20/2006 (format mm/dd/yyyy)
The time as well say in f3
=timevalue(d3) giving 14:16 (format hh:mm)
Then the serial numbers can be added say in g3
=e3+f3 giving 09/20/2006 14:16 (format mm/dd/yyyy hh:mm)
Some of the formats may need to be added as Custom if not already available
HTH
Anthony
"Arun2902" wrote:
[Quoted Text] > Hi,
>
> Hope someone can help me with this. I am trying to convert "Wed. Sep. 20,
> 2006 14:16" into a format recognized by excel (9/30/06 14:16). Have tried to
> extract and collate the data using
> "=CONCATENATE(VALUE(RIGHT(LEFT(B3,12),2))," ",RIGHT(LEFT(B3,8),3),"
> ",RIGHT(LEFT(B3,18),4)," ",RIGHT(B3,5))", but all my efforts are in vain when
> I try to convert it to a date and time format recognized by excel. The lenght
> of the text that I'm trying to convert is exactly 24 characters.
>
> Hope someone can help me with this.
>
> Thanks
>
> Arun
|
|
Try this
=--MID(SUBSTITUTE(A1,".",""),5,99)
Format cell as m/dd/yy hh:mm
"Arun2902" wrote:
[Quoted Text] > Hi,
>
> Hope someone can help me with this. I am trying to convert "Wed. Sep. 20,
> 2006 14:16" into a format recognized by excel (9/30/06 14:16). Have tried to
> extract and collate the data using
> "=CONCATENATE(VALUE(RIGHT(LEFT(B3,12),2))," ",RIGHT(LEFT(B3,8),3),"
> ",RIGHT(LEFT(B3,18),4)," ",RIGHT(B3,5))", but all my efforts are in vain when
> I try to convert it to a date and time format recognized by excel. The lenght
> of the text that I'm trying to convert is exactly 24 characters.
>
> Hope someone can help me with this.
>
> Thanks
>
> Arun
|
|
|