Converting Date text to Date

Discuss the spreadsheet application
Post Reply
tadin
Posts: 7
Joined: Sat Feb 16, 2013 3:34 pm

Converting Date text to Date

Post by tadin »

Hello,

I get some Excel files saved as csv files with date and time columns .
Naturally, in csv the dates are interpreted as text, thus when saved as csv, the Excel adds a " ' " before the text ('01-01-2013), which is OK.
The problem is that when I set the column in OO Calc to Date, it does not remove the text designator, and I can not even remove it by find-replace. (can erase one by one, but I have thousands...)

Any idea on how I can make a date text into relevant Calc date format?
How to change date format into date number?
How to remove the text designator from any cell?

Thanks.
Open Office 3.3 Win7 Home Premium
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting Date text to Date

Post by Villeroy »

Import the date column as date. Mark the column and choose a date format MDY or DMY.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tadin
Posts: 7
Joined: Sat Feb 16, 2013 3:34 pm

Re: Converting Date text to Date

Post by tadin »

Thanks Villeroy, but there is no 'import' operation.
There is a csv (comma-separated-values) file that by definition has no formatting, and thus when saved treats all as text.
The file made by Excel precedes each cell with the " ' " (Hex 27) as text designator (I believe it is like"@" in Calc).
But the question is more general: How can one convert text columns into values?
And, why am i unable to remove the ' by replacing it? (The Calc does not find it!).
Open Office 3.3 Win7 Home Premium
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting Date text to Date

Post by Villeroy »

Of course you import text data when you load csv into a spreadsheet program. This is why you get the text import dialog where you can specify the assumed locale, column separators, encoding and more details for specific columns.

If you do not see any text import dialog, then the whole file is not a text file and therefore it should not carry the .csv ending in the file name. Of course you can tell Excel to treat dates correctly which would eliminate the problem at the data source.

Calc can convert text to numbers (and vice versa) with a simple replacement.
1) Select the column(s) in question.
2) Edit>Find>Replace...
[More Options...]
[X] Current selection only
[X] Regular expressions
Search: .+
Replace: &
[Replace All]

This replaces any content with the same effectively re-typing all data.
The very procedure converts data into text if you apply number format "@" (text) before the replacement.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Converting Date text to Date

Post by acknak »

If you follow Villeroy's suggestions and still aren't getting what you want, please attach a sample data file here so we can see exactly what you're dealing with.

The forum will require you to put the csv file in a zip archive, then you can attach it, as long as the zip is less than 128k.
AOO4/LO5 • Linux • Fedora 23
Brian_M
Posts: 1
Joined: Fri Jan 22, 2016 12:53 pm

Re: Converting Date text to Date

Post by Brian_M »

Villeroy wrote: Calc can convert text to numbers (and vice versa) with a simple replacement.
1) Select the column(s) in question.
2) Edit>Find>Replace...
[More Options...]
[X] Current selection only
[X] Regular expressions
Search: .+
Replace: &
[Replace All]

This replaces any content with the same effectively re-typing all data.
The very procedure converts data into text if you apply number format "@" (text) before the replacement.
This worked for me (although, as primarily an Excel user, I've no idea how the conversion works) - just blindly followed the instructions and it sorted me out perfectly, thanks!
ApacheOffice 4.2 Win 7 64
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Converting Date text to Date

Post by acknak »

Brian_M wrote:... I've no idea how the conversion works ...
Nothing magic. It's just a matter of tricking Calc into re-scanning the text values and doing the normal date conversion.

Calc normally converts any entry that looks like a date into a date value. This conversion can get bypassed, leaving as plain text an entry that Calc would normally recognize as a date. If we can arrange for Calc to re-scan the text value, as if it were entered by the user, Calc will then make the conversion. One way to force this "re-entry" is by find/replace. The extra stuff there is just a search that matches anything and replaces it with what was already there. This forces Calc to re-scan the text and do the date conversion.
AOO4/LO5 • Linux • Fedora 23
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting Date text to Date

Post by Villeroy »

Brian_M wrote:This worked for me (although, as primarily an Excel user, I've no idea how the conversion works) - just blindly followed the instructions and it sorted me out perfectly, thanks!
In Excel or any other spreadsheet program enter a valid date with a preceeding apostrophe into some blank unformatted cell. This is one way how to enter numeric expressions as literal text without any evaluation taking place. The text will be aligned to the left cell border.
Now re-enter the same text into the same but this time without the leading apostrophe. Your input will be recognized as a numeric value and the number will be formatted in some default date format (usually with 2 digit day, 2 digit month and 2 digit year). Like any other number, the value will be aligned to the right border. When you hit Ctrl+F8 in Calc (or menu:View>Highlight Values), the value will appear in blue font which indicates that this value is a constant number.
The replacement does nothing but re-entering whatever cell text.
This method may fail:
Text "12/01/2015" converted to number may give 1st of December in US context or 12th of January for the rest of the planet.
Text "13/01/2015" may remain a text value in US context because there is no 13th month or yield 13th of January.
Before you start the replacement you should ensure that the number format language provides the right context for the conversion.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Martin.Flynn
Volunteer
Posts: 96
Joined: Wed Nov 28, 2007 7:52 pm
Location: Moray

Re: Converting Date text to Date

Post by Martin.Flynn »

Perhaps I've missed something in this post but should this not be solved with the operation "Data>text to columns"?
Rip Van Apache Open Office 4.1.3 / Libre office 5.3.5.2 on Ms Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Converting Date text to Date

Post by Villeroy »

Martin.Flynn wrote:Perhaps I've missed something in this post but should this not be solved with the operation "Data>text to columns"?
Text-to-columns works just as well. If the locale needs to be adjusted you have to change the global locale, do the conversion and then change back to your preferred locale.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Converting Date text to Date

Post by keme »

tadin wrote:Thanks Villeroy, but there is no 'import' operation.
There is a csv (comma-separated-values) file that by definition has no formatting, and thus when saved treats all as text.
Opening a CSV is an import of sorts, and will convert numbers to numerical cell values where possible.
tadin wrote:The file made by Excel precedes each cell with the " ' " (Hex 27) as text designator (I believe it is like"@" in Calc).
That apostrophe is the spreadsheet software's convention for indicating that content which looks like a number is in fact text. Same for Excel and Calc.
tadin wrote:But the question is more general: How can one convert text columns into values?
Already mentioned: Select the dates column and menu item data - text to columns. Select the columnn in the conversion dialog, an then pick the column type corresponding to the date format in use.
If you want to keep the original text column, the VALUE() function is another approach, not yet mentioned as far as I can tell. Note that with VALUE(), Calc will evaluate the string based on OpenOffice locale settings (disregarding language settings for cells), and display according to selected date format. IOW, in many cases not as useful/reliable as text to columns.
tadin wrote:And, why am i unable to remove the ' by replacing it? (The Calc does not find it!).
The apostrophe is not part of the cell content as such, but rather a "type modifier" for the content.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Post Reply