• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Trying to convert a column of data into date format in Excel

Hi all,

I exported data from a program which I have openned in excel. The date and times have added an " ' " symbol. Specifically, in the date column, the data I have in the columns is:
'19/05/2011
'24/05/2011
'24/05/2011
'24/05/2011

In the time column (different column), the data I have is:

'1:52:35 AM
'9:34:21 AM
'1:12:06 PM
'1:14:04 PM

I know if I remove the " ' " character, excel is able to recognise the data as a time / date format. However, I tried functions in excel which removes the first character in a cell but that does not seem to work.

Any advice is greatly appreciated
0
Singamajig
Asked:
Singamajig
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

is the ' sign visible in the cell, or just in the formula bar?

If you can see it in the cell, then you could do this:

Insert an empty column to the right of the column with the date
Select all the dates and click
Data > Text to columns > Delimited > tick Other and enter a ' in the box > Finish

Do the same for the time values and delete the empty columns


If you can see the ' sign only when you edit the cell in the formula bar, use a formula in a helper column.

=A1+0

Format the result as a date, copy down. Do the same for the time values and format as time. Copy the resulting columns and use Paste Special > Values to paste over the original data. Delete the helper columns

cheers, teylyn
0
 
SingamajigAuthor Commented:
Thanks Teylyn,

I could not see the ' sign in the cell.

Your second method works great.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now