CODEWITHSUNDEEP
exceldate
Matt
Matt

Reputation: 95

Excel dates - converting different date formats into a single format

enter image description here

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.

Upvotes: 0

Views: 683

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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

  • Change the output of the data so as to have the dates in DMY format.
  • Instead of 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.

Upvotes: 2

Ani Menon
Ani Menon

Reputation: 28199

Write a IF() where if column B specifies "Custom" change format to standard.

For AM, just remove it(replace AM with nothing). For PM add 12 to the hours and replace PM with nothing.

For custom Replace - with / and append :00 to add the seconds to the custom ones too.

Upvotes: 0

Related Questions