0

In a CSV file I have data in this format

24/12/2010 7:24:56 AM

in older versions of excel just opening the file it recognizes it as time.

In 2007 it doesn't....and I'm not quite sure how to get Excel to recognize it as a date time.

Improve this question
4
  • importing it into google spreadsheets worked automatically as well, without having to do anything special
    – Keith Nicholas
    Jan 25, 2011 at 1:37
  • What happens if you press "F2" to edit the field, then "Enter" to confirm (with no changes)?
    – user3463
    Jan 25, 2011 at 1:51
  • nothing in particular
    – Keith Nicholas
    Jan 25, 2011 at 2:41
  • Is your computer set to US date formats, and therefore expecting mm/dd/yyyy?
    – Diem
    Feb 5, 2011 at 23:43

2 Answers 2

Reset to default
0

Use Data|Text-to-Columns to parse the date as DMY:

  • Click Text to Columns
  • Make sure Fixed width is selected
  • Put a column break between the date and time and remove all other column breaks
  • For the first column, select Date format, and in the dropdown box next to it, select DMY
  • Click Finish

This will parse the date into American date and time, but in separate columns. To put them back into a single column, create a formula in the column next to the time column, adding the two together. For example, if your dates start in A1 and your times start in B1, in C1, just enter =A1+B1 and copy that down for the rest of your date values.

Improve this answer
0

In some cases, you can also multiply the value by one to force Excel translate a text into a number (when possible).

A1 = 24/12/2010 7:24:56 AM
B1 = A1 * 1

Hope it helps.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .