Text to Date - "Thu Nov 23 16:23:42 GMT+05:00 2017"

[SAADIS87]

Hi,
Im super new to this and new to Apache.
I have a csv file with dates in the following format "Thu Nov 23 16:23:42 GMT+05:00 2017"

They are imported as text.
I would like to convert them to a date format so I can use them for analysis further.
Pls help.

Thanks
Saadi

[robleyd]

Assuming all the imported 'dates' have the exact same format then the attached file shows one way of getting a date from the text. If you need time as well, use the TIME function in a similar manner to how I've used the DATE function. Format the result column to display the date as you need it - YYYY-MM-DD etc. Note, this is a quick and dirty solution; better would be to convince whoever supplies the CSV file to use the ISO 8601 standard format for dates.

You can of course combine all the formulae into one if you prefer; I've left them separate to better demonstrate what I have done.

If you are new to spreadsheets, you may find the following to be useful resources.

[Tutorial] Ten concepts that every Calc user should know
OpenOffice Spreadsheet Tutorial for Beginners with Examples
[Tutorial] Absolute, relative and mixed references
[Tutorial] VLOOKUP questions & answers
[Tutorial] How do I specify the formula for a column?
https://wiki.openoffice.org/wiki/Docume ... /DataPilot

[Villeroy]

menu:Tools>Options>LanguageSettings>Languages>Locale = English(any country)
LEFT(A1;11) returns the first 11 characters (3 day name, space, 3 month name, space, 2 day digits, space)
RIGHT(A1;4) returns the rightmost 4 characters (4 year digits)
LEFT(A1,11)&RIGHT(A1,4) concatenates the 2 to "Thu Nov 23 2017"

Code: Select all   Expand viewCollapse view

=VALUE(LEFT(A1,11)&RIGHT(A1,4))

interpretes this text in the given locale contxt and returns the right day number which can be formatted any way you want. Basically we strip off the time and time zone info.
The solution by robleyd is better because it does not depend on the locale. It subtracts the 4 numbers year, month, day that make up a valid day number. DATE(y;m;d) returns the day number for the given 3 numbers.

[Lupp]

(Spoken aside.)

The inventiveness of humans in contriving absurd date-time formats is overwhelming.

[RoryOF]

OT: the poet Hilaire Belloc wrote a series of mottoes for sundials. Of these, my favourite is

"I am a sundial: ordinary words
cannot express my thoughts on birds."