[Solved] Calc keeps wanting numbers to be dates

Discuss the spreadsheet application

[Solved] Calc keeps wanting numbers to be dates

Postby tauaru » Sun Feb 03, 2008 9:11 am

I am using OO.O V2.3.1 on a Windows XP laptop.

I have formatted the columns in the spreadsheet so that some are currency and some are dates. However, whenever I type a date in Column A and then a number in Column B, the number immediately turns into a date. Furthermore, the date field is formatted with a border on the left side, Column B is taking on the formatting of Column A including the border. This happens whether I Tab, Enter or mouse to the next cell.

This issue is decreasing the efficiency of OO. Has anyone encountered/solved this?
Last edited by Hagar Delest on Fri Nov 23, 2012 11:58 pm, edited 1 time in total.
Reason: tagged [Solved].
tauaru
 
Posts: 2
Joined: Wed Dec 12, 2007 5:28 am

Re: Calc keeps wanting numbers to be dates.

Postby Villeroy » Sun Feb 03, 2008 12:14 pm

First remove all formatting. This makes spreadsheet design much easier:
Select used range and double-click cell style "Default" in the stylist window (F11).
Hit Ctrl+Shift+Space to remove all hard formatting.

Now, how do you enter a decimal? 3.142 (point) or 3,142 (comma). The decimal separator should match with your office locale in "Menu:Tools>Options>LanguageSettings>Locale Setting". If this setting is "Default", then the decimal separator should match with your operating system's locale (disregarding any customizations you may have done to your operating system's locale).
If your office operates in a German or French environment, input "3.", "3.2", 3.2.", 3.2.8", "3.2.2008" and several others all evaluate to the date "2nd of February 2008" because point is a common date separator in both countries Germany and France.

If you want to use another decimal separator you can do this on 3 levels:
Switch the whole office to another locale (independent from GUI language): "Menu:Tools>Options>LanguageSettings>Locale Setting"

Switch the numeric context of selected cell ranges to another locale:
Option "Language" in Menu:Format>Cells... Tab:Numbers
You should create a cell style for this formatting.

Switch the numeric context of all cells in this document to another locale:
Option "Language" in Menu:Format>Cells... Tab:Numbers of cell style "Default"

Font-formatting includes another language setting which involves some typographic features and spell checking for text values in cells.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26635
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc keeps wanting numbers to be dates.

Postby kingfisher » Mon Feb 04, 2008 1:29 am

FWIW, I have formatted two columns in the same way without having the same problem. If you can't solve the problem using Villeroy's suggestions or other methods, I'd create a new file and start again.

You are better off using styles rather than hard formatting. Try creating a date style and a currency style using F11.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Calc keeps wanting numbers to be dates.

Postby tauaru » Mon Feb 04, 2008 6:48 am

Thank you Villeroy. The problem seems related to the Decimal being pegged as the date separator. I changed that and everything is fine. It still doesn't explain why OO overrode my hard formatting. That seems silly.
tauaru
 
Posts: 2
Joined: Wed Dec 12, 2007 5:28 am

Re: Calc keeps wanting numbers to be dates.

Postby h1h » Tue Feb 05, 2008 1:16 am

maybe unchecking "expand formatting" in options will help
OOo 2.4 (from OOo-site) on Linux
User avatar
h1h
 
Posts: 40
Joined: Wed Nov 28, 2007 8:45 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 28 guests