1

The image below shows different formats for DOB, what is the easiest way to format them to dd/mm/yyyy? The dates on the right are correct however the dates on the left are back to front and missing a 0 for all single numbers.

enter image description here

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Pete
  • 171
  • 1
  • 5
  • 22

1 Answers1

2

I would be willing to bet that 08/03/1997 was not originally 08-Mar-1997 but started out as 03-Aug-1997. Same goes for all of the other ambiguous DMY/MDY dates that Excel wrongly converted during the text import. Some dates remained as text because (as in A3) there are not 13 months in a year.

It makes no sense to convert the rest of the data now that half of it is already wrong. Abandon the import and then import it properly.

I could regurgitate the narrative from Excel VBA - Convert Text to Date but it has already been adequately described there. In short, bring the text back in with Data ► Get External Data ► From Text and specify the correct date conversion mask in the Text Import wizard. In VBA, use the Workbooks.OpenText method and specify the xlColumnDataType as MDY.

Community
  • 1
  • 1