Convert a dates from Tweet hydrator to standard date format in Google Sheets mm/dd/yyyy-CodePudding

I have a large dataset (close to 80,000) of tweets dated like this:

Wed Oct 05 01:20:53 0000 2016

What script can I run to convert the dates in Google Sheets to the simple mm/dd/yyyy form? In this case, it should be: 10/05/2016 Thanks!

CodePudding user response：

If the format of the date is you mentioned is consistent, you can use the below formula (assuming the date is in cell A1)

=DATEVALUE(RIGHT(A1,4) & MID(A1,5,3) & MID(A1,9,2))

This will extract the Datevalue from the string and then you can format it to look in the mm/dd/yyyy format

CodePudding user response：

Try

=arrayformula(if(A1:A="",,1*(regexextract(A1,"\d{2}")&"/"&regexextract(A1,"\D  (\D ) ")&"/"&regexextract(A1,".* (\d )"))))

or (with hours/minutes/seconds)

=arrayformula(if(A1:A="",,1*(regexextract(A1,"\d{2}")&"/"&regexextract(A1,"\D  (\D ) ")&"/"&regexextract(A1,".* (\d )")) regexextract(A1,"\d{2}:\d{2}:\d{2}")))

and define the appropriate format

CodePudding user response：

Another solution

=index(ifna(Text(1&RegexExtract(A:A,".*?\s(.*?)\s"),"MM")&"/"&RegexExtract(A:A,"\d{2}")&"/"&RegexExtract(A:A,".*\s(.*)")))

=index(text(regexreplace(regexreplace(A:A,"\ 0000\s",),"(.*)(\d :\d :\d )\s(.*)","$1$3 $2"),"mm/dd/yyyy"))