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

  • The Sheet linked doesn't have its permissions set correctly. I can't access it. – AccioBooks Jan 30 '16 at 2:34
  • Does "the date column" actually contains dates, or just text? Impossible to tell since the spreadsheet isn't shared. – user79865 Jan 30 '16 at 5:29
  • The date column has text in it. I just updated the link if you need to view it. – Vee Feb 1 '16 at 1:34
up vote 1 down vote accepted

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")
  • Hi Aurielle, the formula is perfect! Thanks a bunch! :) – Vee Jan 30 '16 at 3:18
  • follow up question please (I dont quite understand how the formula works), what if the date is in this format: Wed, Mar 4, 9:00AM and again I want it to be "MM/DD/YYYY", how should I modify the formula? – Vee Jan 30 '16 at 3:24
  • @Vee I updated my answers up top for you – Aurielle Perlmann Jan 30 '16 at 5:08

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.

  • Hi AccioBooks, thanks for the help. I actually tried that but it didn't work. – Vee Jan 30 '16 at 3:19
  • @Vee Oh, sorry. – AccioBooks Jan 30 '16 at 3:20
  • I expanded your answer; otherwise it would likely be deleted after Low Quality Posts review. "This link might help you: [link]" is never a good answer. – user79865 Jan 30 '16 at 21:57
  • @LiveForever Sorry. I probably should have put it as a comment. – AccioBooks Jan 30 '16 at 21:59

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.