Binary file downloaded from the bank

I downloaded a file from the bank with a csv extension. I was able to open it in Excel by double clicking and then save it with an xls extension. I was also able to import it as text. It does not appear to make a difference how the file was opened. The issue is the date format. The left column is the date expressed as an example 12/31/2012. Excel does not seem to recognize this as a date. It will not allow me to change to any other format such as 31-Dec-12.

Macbook Pro
Operating system OS X 10.6.8
Excel for Mac 2011

I appreciate that the data will be sensitive, so can you delete everything except for the date column, save that with a different file name, and then post it to here so that we can see the variations that you get. Do you get some dates, and are some dates recognised incorrectly? i.e. does 7th December show up as 12th July?

Pete

Okay, thanks. It looks like all the dates have been brought in as text values. In your real file, insert a new column B and put this formula in B1:

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

then copy down to the bottom of your data (just double-click the fill handle). Then fix the values in column B, and then delete column A.

Hope this helps.

Pete

Thank you Norie. That worked. Wonderful.

Thank you Pete. I used the approach suggested by Norie with great success. I appreciate your help.

@AlKey:

that will only work for those countries which have the regional date setting as mm/dd/yyyy - it doesn't work for me in the UK.

Pete

I'm in the UK

Well, try Alkey's method, then, Norie.

Pete

Originally Posted by AlKey

This is strange and I didn't know that.

I've come across it many times with different date formats.

Does the serial number different in UK than the one used in the USA?

No, it's the same. It YOU were to enter a date into a cell as 30/09/2013, what will you get? It will probably be treated as a text value because you don't write your dates like that. Similarly for me if I enter the date as 09/30/2013.

Excel takes what you have entered and tries to make sense of it - it will be converted into a serial number ONLY if Excel thinks it is a recognised date format (and that it makes sense).

Hope this helps.

Pete