Multiple date formats in one field

[tonypreece]

Hi folks,

I've received a csv file where the date filed contains multiple different date formats: dd/mm/yyyy, dd/mm/yy, dd-mm-yyyy, ddmmyyyy. Also silly errors like dd/mm/yyy.

I've resolved most of the errors but I'm looking for a way to fix the rest handful of rows where the date is either:

dd/mmyyyy

dd/mm/yyy (digit missing from the year)

Can anybody recommend a nice way to find and fix these fields?

[CampbellRG]

@BrandonB 

Awesome.

Thank you!

[BrandonB]

You will need to be cautious of situations like 9-Jun-20 though because if you ran that today it would use 2020 and it isn't clear how you decide which one to use when the year is not provided. 

[kimikoxavier]

@jdunkerley75 If it's not too much could you please explain what the meaning of [/\-] is in the formula?

[CampbellRG]

@kimikoxavier That's just telling which punctuation to remove from the data, so it will remove / or \ or - if it's in the date.

[kimikoxavier]

Got it. Thanks so much!