Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

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

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.

share|improve this answer

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.

share|improve this answer

You must log in to answer this question.

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