[Solved] Calc - date format and an invisible apostrophe

[pyrogerg]

Hello,

I'm new to this forum and new to OpenOffice, and I need help. I have a spreadsheet of field data and one of the columns is for "date". When my friend initially entered the dates, he used two different formats for some reason: 12/31/2007 and 31 Dec 2007. I've formatted the cells for dates and I'm trying to covert all the dates to the correct format. No problem for 12/31/2007; that's done. The format 31 Dec 2007 is giving me some trouble.

When I look at the formula field for an individual cell, I see an initial apostrophe (i.e. '31 Dec 2007). That character doesn't show up in the column view until I click in the formula field, however. If I remove the apostrophe, Calc recognizes the date and converts it to the correct format. My first thought was to find and replace the apostrophe. That should make short work of the +/- 1,500 instances. The find and replace function can't find it, unfortunately. I even copied and pasted the apostrophe from a field to be sure that it wasn't an issue like curly/straight.

Any ideas?

Thanks in advance.

[pingju]

Select one cell with the date has problem, right click -> Format cell -> Numbers, to check the format code to see if there is anything special.

Normally you can display all the date in a format required by setting the correct format code, select a default one or fill in a customized one.

[pingju]

Ignore my last post.

1. If all the wrong date are in column A, such as you have '21 Dec 2009 in A1
2. In B1, input: =Value(A1)
3. Select B1, right click -> format Cell -> Numbers -> date -> choose the required format
4. Apply this to all 1500 rows in 10 seconds.

[pyrogerg]

Pinju,

When I format cell/number/date I just get a string of "###". Before the format change I have a number like 39727 where the date was "'06 Oct 2008". Am I doing something wrong?

[Villeroy]

What is the locale in menu:Tools>Options...LanguageSettings>Languages?
I guess it's "English(US)" or in case of "Default" your operating system runs a US style locale.
"###" in a spreadsheet indicates that the column is too narrow to show the numeric value.

12/01/99 is a day number in January, 01/12/99 is a day number in December, 31/12/99 is no number at all, although it would be a number in most other locale contexts.
When you change the locale of your text value "31/12/99" to something British, Canadian or similar, the text won't be converted to a day number in December. Instead your text value gets a preceeding apostrophe to tag this as text and prevent conversion.

No formatting attribute will ever change your values. No border, no color, no font, no number format nor locale. All numbers remain the same numbers, all strings remain the same strings.

VALUE(A1) will convert text value "31/12/99" in the context of the locale setting. It returns #VALUE in US context and 31th of December in other context.
When you re-enter the text without the quote, it will evaluate to a day number. In that case you, the user of the spreadsheet, entered a new value which evaluates to a day number.


How to convert numeric text to number by re-entering everything in one step.
Select the cells in question.
Apply any number format other than "@" (text)
menu:Find&Replace...
[More Options...]
[X] Current Selection
[X] Regular expression
Search: .+ (a dot and a plus)
Replace: &
[Replace All]

How to convert numbers to text by re-entering everything in one step.
Apply number format "@" (text)
Do the same replacement as above into cells that have been prepared to take all new input as literal text.

[pingju]

All date is saved as a value internally in Calc. It gives the date when you set a format for the value.

If you did what I described in my last post, you should get the dates corrected.

Attached please find an example I tried with your data.

[Villeroy]

That should make short work of the +/- 1,500 instances

You imported from csv or imported text otherwise didn't you?
You run a non-US locale while the imported dates assume US locale or vice versa, right?

Repeat the text import.
In the import wizard right-click the column in question and set it to either one of "Date (MDY)" or "English (US)" and everything should import well regardless of the locale. Once you've got the correct values you can format them to your liking.

[pyrogerg]

Thanks Villeroy and Pinju,

You both solved it. Villeroy, I didn't import the data into the spreadsheet, I got it from a colleague (scientist) who had entered it in Excel. Re-importing it wasn't an option for me, but it didn't need to be. You were certainly right about the language conventions for date format. Converting from text to number and then to date got everything looking like it should. Thanks again.

p.s. I added "[Solved] to the subject line of this post, but if I'm supposed to edit the subject line for the whole thread I don't know how.

[Villeroy]

Villeroy, I didn't import the data into the spreadsheet, I got it from a colleague (scientist) who had entered it in Excel.

For the records:
The main incompatibility between recent versions of Excel and any other spreadsheet is:
Excel magically treats numeric text values as if they were numbers. I believe that most Excel users are totally unaware that their "numbers" evaluate differently when they pass over the same file to an owner of the same Excel version running in another locale context.
"123,000" is 123 point zero in some languages and "123.000" evaluates to 123000. "12/01/99" is not 12 of January in the US.
OOo 3.2 will introduce a similar magic converter. However, that one will only convert unambiguous strings.
Integer numbers consisting of digits only and ISO date/times like 2009-12-31 13:45:59.789 (or with comma for the fractions of seconds)
Without clear, unambiguous numeric strings Calc will display errors.
So this new "feature" will cost some computer performance without providing significantly more Excel compatibility.
Showing more errors in badly designed Excel sheets is the right thing, though

[ayola]

Try
: http://extensions.services.openoffice.o ... roject/CTI

Regards

[acknak]

Sorry, that link does not work for me; what is that extension supposed to do?

PS: OK, I guess this is it: http://extensions.services.openoffice.o ... nload/4559

CTI : The Easy one click convertion between textual and numerical formats of numbers in cells.

[Villeroy]

The CTI macros may not work due to hard coded assumptions whereas the older extension I tested some year ago used to fail when the cell locale is not the same as the application locale.

The simple find&replace can be adjusted and tweaked in many ways to deal with all kinds of localized numeric strings. The macro solutions are not language aware and obfuscate everything.

btw: The extensions site is crawling as if it were under DOS attack.

[sreeramvaram]

Thank you for the post, it worked and was really helpful

What is the locale in menu:Tools>Options...LanguageSettings>Languages?
I guess it's "English(US)" or in case of "Default" your operating system runs a US style locale.
"###" in a spreadsheet indicates that the column is too narrow to show the numeric value.

12/01/99 is a day number in January, 01/12/99 is a day number in December, 31/12/99 is no number at all, although it would be a number in most other locale contexts.
When you change the locale of your text value "31/12/99" to something British, Canadian or similar, the text won't be converted to a day number in December. Instead your text value gets a preceeding apostrophe to tag this as text and prevent conversion.

No formatting attribute will ever change your values. No border, no color, no font, no number format nor locale. All numbers remain the same numbers, all strings remain the same strings.

VALUE(A1) will convert text value "31/12/99" in the context of the locale setting. It returns #VALUE in US context and 31th of December in other context.
When you re-enter the text without the quote, it will evaluate to a day number. In that case you, the user of the spreadsheet, entered a new value which evaluates to a day number.


How to convert numeric text to number by re-entering everything in one step.
Select the cells in question.
Apply any number format other than "@" (text)
menu:Find&Replace...
[More Options...]
[X] Current Selection
[X] Regular expression
Search: .+ (a dot and a plus)
Replace: &
[Replace All]

How to convert numbers to text by re-entering everything in one step.
Apply number format "@" (text)
Do the same replacement as above into cells that have been prepared to take all new input as literal text.

[alavarre]

Villeroy, thank you for this solution. I was tearing my hair out:

viewtopic.php?f=9&t=22539&start=0
How to convert numeric text to number by re-entering everything in one step.
Select the cells in question.
Apply any number format other than "@" (text)
menu:Find&Replace...
[More Options...]
[X] Current Selection
[X] Regular expression
Search: .+ (a dot and a plus)
Replace: &
[Replace All]

Best regards, Andy

[Villeroy]

When importing dates simply check option "Detect Special Numbers" and the right locale which conforms with the date expressions to be imported and everything will be fine without fixing things afterwards.

The screenshots shows numbers with point decimals and $-signs.
Any M/D/Y dates would be imported correctly with US-English locale and "Special Numbers" option.

[alavarre]

Thanks. Our problem was not with dates, rather currency in an HTML table. The copying and pasting delivered the correct column contents, but the currency column had this hidden "apostrophe" that your solution resolved. I've written the solution in my journal for the future!

Thanks again.

Best regards, Andy

[jrkrideau]

I didn't import the data into the spreadsheet, I got it from a colleague (scientist) who had entered it in Excel.

All spreadsheets are dangerous; Excel is more dangerous than most.

I added "[Solved] to the subject line of this post, but if I'm supposed to edit the subject line for the whole thread I don't know how.

Go to the top of the thread and you should see a button on the upper right of the window [the EDIT button] that will let you apply a Solved check mark to the thread.