2

I have some cells in the format 15/05/15. Here is an example. These are dates, but as I copied them into Google Spreadsheets, they are apparently treated as strings. How do I convert them into date objects, preferably in the format 2015-05-15?

Clearly I should be choosing Format->Number and then a date format, choosing MM/dd/YY if needed to pin it down. I might then re-select and choose another format like the YY-MM-dd that I need. But this does nothing.

(Actually, in some cases I got this sort of thing to work and in some cases I did not. I am not sure what makes the difference. Maybe ambiguous strings?)

How do I convert the dates?

Improve this question
1
  • The question is only about Google spreadsheets, the Google Drive tag should not be included.
    – Rubén - Volunteer Moderator - ♦
    Commented Jun 3, 2015 at 5:47

1 Answer 1

Reset to default
8
+100

The relevant command is DATEVALUE, which converts a string to date object. However,

Understood formats include any date format which is normally autoconverted when entered, without quotation marks, directly into a cell. Understood formats may depend on region and language settings.

Since these are not understood when pasted in, chances are that DateValue would not understand them either. So you'll need to rearrange the string from dd/mm/yy into the universally digestible yyyy-mm-dd first: 

=DATEVALUE(20&RIGHT(A3,2)&"-"&MID(A3,4,2)&"-"&LEFT(A3,2))

The inner commands turn, say, 17/05/15 into ISO 8601 2015-05-17, which DATEVALUE understands without ambiguity.

Now that you will have a date object, you can pick a formatting for it by usual means, via Format menu.

(Remark: based on your sample, I'm assuming that both month and day always have two digits. If you had other variations like 17/5/15 or 2/3/14, it would be better to use SPLIT(A3,"/") to divide the string correctly, and then reassemble by concatenation as above.)

Improve this answer
4
  • Thank you. I added your code into that example spreadsheet. It's what I need!
    – Joshua Fox
    Commented Jun 3, 2015 at 3:53
  • 1
    That documentation is a bit mysterious. Of course I can change region/language settings to accommodate the input format. But they are not clear on what they accept. If I input a block of dates like in the example spreadsheet, the code should be able to take a good guess at what it means and convert it to date objects, which I can then reformat to ISO 8601. I wonder if it is documented anywhere. "Understood formats include any date format which is normally autoconverted ....Understood formats may depend on region and language settings."
    – Joshua Fox
    Commented Jun 3, 2015 at 3:56
  • 2
    Yes, that's why I didn't even want to try playing with those settings. Relying on an undocumented algorithm of parsing non-ISO date strings wasn't attractive. (Unrelated but fun fact: in JavaScript, both Date.parse("2015-06-03") and Date.parse("2015-6-3") accept the input, yet return different outputs.)
    – user79865
    Commented Jun 3, 2015 at 4:07
  • Thanks, it does seem like the real answer is nowhere but the source code.
    – Joshua Fox
    Commented Jun 3, 2015 at 8:05

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.