Unable to format date properly

Hi, I'm having a hard time trying to format a date column properly. I'm using

Formula:

Please Login or Register  to view this content.

but it seems to be only working for some cells but not the others.

The date in A1 is formatted as m/dd/yyyy (eg. 8/28/2015 1:33:37 PM) and all the cells are in General format.

Also on the cells that seems to 'work', the month is wrong. For example 9/4/2015 4:25:04 PM is now considered as April which is incorrect and should have been September instead.

Does anyone have any ideas how to correct this?

Thanks in advance.,

Thanks for your responses. Is there a way to convert the text into a proper date? The excel file is extracted directly from a system and it is formatted as such. I don't think it is possible to change it in the system.

Originally Posted by treeantz

Thanks for your responses. Is there a way to convert the text into a proper date? The excel file is extracted directly from a system and it is formatted as such. I don't think it is possible to change it in the system.

The Text To Columns feature might work for you. Select a column range of dates, click Data > Text To Columns, then click Next twice (changing options as needed). Under Column Data Format in the third context menu, select Date and select the form of the input date (DMY) from the drop-down list, then click Finish.

If the original source is a text file, you can use a similar option of the Get External Data wizard.