How to change format from General to Date/Time?

[Joozh]

I'm using Excel-2007. I exported data from a windows application for analysis/calculations and one of the columns is a time stamp for various transactions. I'll call it the TransTime column.

The format of the resultant exported data TransTime column is GENERAL and below is a sample data:

05/13/2010 17:31:36
05/13/2010 17:32:23
05/13/2010 17:32:23
05/13/2010 17:32:23
05/13/2010 17:32:23
05/13/2010 17:32:23​

I tried to apply custom format "dd-mmm-yy h:mm AM/PM" but it had no effect on the TransTime column and it still appears as shown above.

Untill I can change the format, I will not be able to do the various analysis/calculations etc.

Can someone help me.

thanks.

 

[Jon von der Heyden]

Hi

Select the range and use text to column on it. In step one choose Delimited, in step two uncheck all delimiters, and then hit finish.

Next format to the desired date format.

 

[castellan]

This is great! I didn't know I could force Excel (or OpenOffice.org Calc) to interpret a column of oddly formatted text to be interpreted as a column of dates. this worked perfectly!

Thanks!

 

[Joozh]

Hi

Select the range and use text to column on it. In step one choose Delimited, in step two uncheck all delimiters, and then hit finish.

Next format to the desired date format....
[/IMG]

Thank you Jon. Somehow missed checking your reply.

Strange that the solution did not work. I know I must be doing something wrong. Anyway, I ended up solving the requirement manually since I needed the solution really bad when i posted. Again my mistake for missing the reply (sorry).

Looking at the image: Actually the data that I wanted to apply the solution to contains many many columns and there are 6 date and time related columns out of 30. So that's why i posted a simple version of my requirement...

Anyways, just wanted to clarify and mainly to thank you for taking the time to help/reply (especially when you are the Moderator).