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.

  • 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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.