Google Sheets – How to Format Date

google sheets

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

Best Answer

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