Different String Date Formats in same Column

I am looking to clean up a field that is currently in a string format and need to make it a date format. The problem is that the formats in the string field are different.

Ex:

2/21/2021 -> 02/21/2021

4/8/2021 -> 04/08/2021

05/15/2021 -> 05/15/2021

2021/06/25 -> 06/25/2021

I need to get them into a mm/dd/yyyy format and when it starts off with the year I am having trouble with that.

Date Format Issue.PNG

Date Time

Transformation

Accepted answers

DataNath

Do you need the exact data type or is this alright? Workbook attached with 2 different methods.

Mixed date format parse.yxmd

binu_acs

@bburzanko3 another way of doing this with Regex_Match function

DateFormat_13_05.yxmd

All comments

DataNath

@bburzanko3 do they appear as all 3 combinations (dd/mm/yyyy, mm/dd/yyyy & yyyy/mm/dd), or is it just a mixture of mm/dd/yyyy & yyyy/mm/dd?

bburzanko3

They are all either MM/dd/yyyy or yyyy/MM/dd.

DataNath

Do you need the exact data type or is this alright? Workbook attached with 2 different methods.

Mixed date format parse.yxmd

binu_acs

@bburzanko3 another way of doing this with Regex_Match function

DateFormat_13_05.yxmd

bburzanko3

Both of these worked perfectly, thank you!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703