Google Sheets – How to Automatically Convert Data

google sheets

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?

Best Answer

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?

Related Topic