Text to date

[Guest]

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

 

[Guest]

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

 

[Guest]

Try this

=--MID(SUBSTITUTE(A1,".",""),5,99)
Format cell as m/dd/yy hh:mm