Google-sheets – Change a date reference as text to Google Sheets date format

This will fix it for you:

=text(regexreplace("timestamp","^\w{3},",""),"MM/DD/YYYY")

you could also transform to remove the additional time parameter at the end with this:

=text(regexreplace(regexreplace("timestamp","^\w{3}, ",""), ", \d+:\d+\w+",""),"MM/DD/YYYY")

OR if your data consistently is formatted the way you did in your examples (with the commas present) you can simply do this:

=TEXT(REGEXEXTRACT(C10,",(.*),"),"MM/DD/YYYY")

Assuming your string is in cell A1, this formula will convert it to a date. You can then format the date however you prefer.

=date(left(A1,4),mid(A1,5,2),right(A1,2))

Or, take the leftmost four characters as the year, the rightmost two characters as the day, and two characters in the middle starting at position 5 as the month, and convert it to a date.

19961210

turns into

12/10/1996

(standard American date format)

From Google Support:

• date(year,month,day)
• left(string,character count)
• mid(string,start,count)
• right(string,character count)