How to convert text (dd mm YYYY) to date ?

asked 2020-06-30 18:55:21 +0200

tls gravatar image

I receive dates in the following format :

 28 May 2019
 14 June 2019
 04 July 2019
 04 July 2019
 04 July 2019
 20 November 2019

How do I convert this to a date ? When importing, I receive a TimeStamp so I cannot use the import formatting :(

\T,

edit retag flag offensive close merge delete

Comments

Not clear what is "TimeStamp" that somehow prevents using "import formatting" - but this FAQ shows a way. Just make sure to choose the correct date parts order (DMY in your case), and locale (en-US would do, since it has the required month names).

Mike Kaganski gravatar imageMike Kaganski ( 2020-06-30 19:08:49 +0200 )edit

I converted them to dates by using menu Format -> Cells and choosing English(UK) as the locale and the dd mmmm yyyy format from the list. But then I had to edit out the leading apostrophe and space from each one because the entries were originally interpreted as text. (Sorry, the example does not show by color that the entries are now non-textual data.)

28 May 2019
14 June 2019
04 July 2019
04 July 2019
04 July 2019
20 November 2019
ve3oat gravatar imageve3oat ( 2020-07-01 02:20:55 +0200 )edit
1

How to convert text (dd mm YYYY) to date ?

If it really was this format, it would be simpler. But...
What you actually have unfortunately is text as returned under some English locale by =TEXT(myDateValue; "DD MMMM YYYY") if myDateValue is a date in default representation,

If your locale is some English knowing this format and writing the names of months the given way, you can use =DATEVALUE(referencToTheTextRepresentedDate) to convert such a date to spreadsheet representation again. Otherwise you may need to create a little lookup table for the translation of names to numbers.

Locale dependent representations of numeric data (and often beyond) are a mess! Never depend on locales wherever avoidable!
When needing to convert dates to text yourself exclusively use YYYY-MM-DD (ISO 8601).

Lupp gravatar imageLupp ( 2020-07-01 11:07:37 +0200 )edit