I have date information like this:February 1, 2014. I want to transform them into 2/1/2014 in Google spreadsheet. I have hundreds of dates so that I cannot transform them by hand. What should I do?

  • Which date notation is mentioned under Format>Number in your spreadsheet? If MM/dd/yyyy is present, then you must select Lawrence's answer. His solution is most straightforward in this case. My answer is only valid if you can only select from dd/MM/yyyy dates. Date notations are dependant upon locale settings: webapps.stackexchange.com/questions/13824/… Dec 10, 2013 at 19:04

3 Answers 3


The DATEVALUE and formatting, from the Format menu option, result in the following date notation:

February 1, 2014 ==> 01/02/2014

You want, as stated in you question, this:

February 1, 2014 ==> 02/01/2014

What you need to do is the following:


  1. create a new column on the left or right side of the column you want to alter.
  2. add the following formula at the start position, you want the formula to begin:
    =ARRAYFORMULA(IF(ISBLANK(A2:A)=TRUE,"",DATEVALUE(TEXT(A2:A, "MM/dd/yyyy")))) enter image description here

    The TEXT formula will convert the string into the desired format: month/day/year. The DATEVALUE formula will convert the string into a date. The ISBLANK formula, in combination with the IF statement, will only show a result, when the cell/range contains a value. The ARRAYFORMULA will take on the ranges of the ISBLANK and TEXT formulas and show the result of that range.

  3. After you did the above-mentioned, you can copy/paste the values or keep using the newly created column


I've created an example file for you: How automatically convert data in Google spreadsheet?


Select all the cells, click on Format in the menubar
Then click on Number, then the date selection in the menu is the format you want.


Google spreadsheets have a function for this, DATEVALUE(). See this question/answer

  • 1
    The link doesn't describe in detail how to handle this situation. Plus the formula itself will give a faulty date format. Dec 7, 2013 at 21:34

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.