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

Discuss the spreadsheet application

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

Postby SAADIS87 » Mon Jan 21, 2019 12:50 pm

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.

Open office 4.16 on Mac OS
Posts: 1
Joined: Mon Jan 21, 2019 12:46 pm

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

Postby robleyd » Mon Jan 21, 2019 1:46 pm

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
(8 KiB) Downloaded 18 times
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
Posts: 3637
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

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

Postby Villeroy » Mon Jan 21, 2019 2:09 pm

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
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.
Last edited by Villeroy on Mon Jan 21, 2019 3:05 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Posts: 29090
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Lupp » Mon Jan 21, 2019 2:21 pm

(Spoken aside.)

The inventiveness of humans in contriving absurd date-time formats is overwhelming.
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
Lupp from München
User avatar
Posts: 2972
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

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

Postby RoryOF » Mon Jan 21, 2019 2:27 pm

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."
Apache OpenOffice 4.1.8 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
Posts: 31970
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests