Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

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

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

3 Answers

up vote 1 down vote accepted

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?

share|improve this answer
add comment

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.

share|improve this answer
    
The format, wanted by the OP, doesn't exist in the format options. –  Jacob Jan Tuinstra Dec 7 '13 at 21:37
1  
It was in mine...came up as MM/DD/YYYY in that Number menu - see screenshot - drive.google.com/file/d/0B1RhPrxJp7gybzFhaEFiRG5odFE/… –  Lawrence Dec 10 '13 at 0:47
add comment

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

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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