excel - How to convert all the datetimes into one standard format yyyy-mm-dd hh:mm:ss AM/PM in csv?

Question

There are 2 datetime formats in same column of a csv file. How to convert all the datetimes into one standard format yyyy-mm-dd hh:mm:ss AM/PM?

a column of the csv having different datetime formats

question from:https://stackoverflow.com/questions/65950980/how-to-convert-all-the-datetimes-into-one-standard-format-yyyy-mm-dd-hhmmss-am

Answer 1

It seems like your dates are in the wrong format. The dd (day-number) and mm (month) are swapped. Only dates with a dd <= 12 are (wrongly) converted, while months with a value > 12 are ignored and treated as text. Do not open the csv file in a normal way, but import it with the Text Import Wizard (Excel 2016):

1. Create a new file and goto Data -> Get External Date -> From Text
2. Import the csv file
3. In step 2 choose the correct delimiter (probably Comma or Semicolon)
4. In step 3 select the first column in the Data preview and choose Date with the format MDY (not DMY!)

Now the dates will be converted correctly.

5. Select the column with the dates
6. Now go to the Format Cells window by clicking the small arrow button in the bottom-right corner of the Alingment area on the Home tab. Select the date format you want.