Date format in Google Sheets

Question

I have a Google spreadsheet file and I need to convert the date column from this format: Sun, May 4, 2014 4:30PM to this format: mm/dd/yyyy. Basically, I need the date to be in a simple format and take out the “day” and “time”.

How can I do this?

Here is the link to the spreadsheet:

https://docs.google.com/spreadsheets/d/1s4EwFa7r0rdF24w3lAuasfcCw8kf1k4to4Y8dQdZjhI/edit#gid=1354673218

Answer 1

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")

Answer 2

You can use "Custom Date Formatting" to obtain mm/dd/yyyy format; however this requires the datetimes to be entered as datetimes — not as strings of text that look like date and time.

Usually, the difference is clear because under default formatting, datetimes are right-aligned while text is left-aligned.