How to change format from General to Date/Time?

Joozh

Board Regular
Joined
Dec 30, 2002
Messages
114
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,751
Office Version
365
Platform
Windows
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

New Member
Joined
May 19, 2010
Messages
1
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

Board Regular
Joined
Dec 30, 2002
Messages
114
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). :)
 

Forum statistics

Threads
1,082,438
Messages
5,365,531
Members
400,837
Latest member
ELMST616

Some videos you may like

This Week's Hot Topics

Top