5

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?

migrated from superuser.com Dec 2 '13 at 23:02

This question came from our site for computer enthusiasts and power users.

  • 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/… – Jacob Jan Tuinstra Dec 10 '13 at 19:04
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:

Conversion

  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

Example

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

0

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.

-1

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. – Jacob Jan Tuinstra Dec 7 '13 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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