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:


  • The Sheet linked doesn't have its permissions set correctly. I can't access it.
    – AccioBooks
    Jan 30, 2016 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, 2016 at 5:29
  • The date column has text in it. I just updated the link if you need to view it.
    – Vee
    Feb 1, 2016 at 1:34

2 Answers 2


This will fix it for you:


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:

  • Hi Aurielle, the formula is perfect! Thanks a bunch! :)
    – Vee
    Jan 30, 2016 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, 2016 at 3:24
  • @Vee I updated my answers up top for you Jan 30, 2016 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, 2016 at 3:19
  • @Vee Oh, sorry.
    – AccioBooks
    Jan 30, 2016 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, 2016 at 21:57
  • @LiveForever Sorry. I probably should have put it as a comment.
    – AccioBooks
    Jan 30, 2016 at 21:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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