Have a large raw data dump and I am trying to format the dates into a consistent format.
As you can see from the screenshot, there are two main formats, one custom mm-dd-yyyy hh:mm AM/PM and mm/dd/yyyy hh:mm:ss. One is stored as general, while the other as a custom value.
I've tried to =left(A2,8), converting via text() and using text to columns, but can't bring the values to a consistent value.
It appears that your Windows regional settings for Short Date are DMY or similar. It is likely your data dump is in MDY format. That is why A2 and A4 are being converted to "real dates" (although incorrectly), and A3 is not since Excel does not know what do with month = 13. You will note that A2 is 1-Dec-2015 and I suspect that in the original data it is 12-Jan-2015.
EDIT: To expand a bit on the explanation. When something that looks like a date or time is entered into an Excel cell, Excel tries to change the result to a date, parsing the input according to the Windows Regional Short Date format. This sometimes has an undesireable outcome. For example, if your Windows Format is MDY but the date is input as DMY, input with days <=12 will be converted incorrectly, and input with days > 12 will be retained as text. This behavior cannot be "turned off" and causes many complaints from those who want to enter data that looks like a date, but is not. (For example, entering an odds ratio as 1-10 or 12:3 will get converted to a date or a time)
Several options
DMY format.OPENing the data file, do a Text Import. In later versions of Excel, you will find this on the Data Ribbon ► Get External Data ► From Text. This will open the Text import wizard and allow you to specify the MDY format of the incoming data before Excel transforms it.After you have done one of the above, the result will be a "real" Excel date or date/time and you can format it how you like.
