OpenOffice Calc - how to stop it changing numbers to dates?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

cloudburst7

Thread Starter
Joined
Aug 31, 2008
Messages
154
I have a table in which the first column is numbers. Sometimes there is a range eg 3-4 in which Calc auto changes it to a date eg 3-4 becomes 03/04/08

I've tried rummaging through its menus but can't find the solution. Changing the date format for a particular cell makes that cell look ok, but I'd have to do that for individual cells becuase copying it to the whole column spoils those cells which contain a single digit, not a range.

I'm sure there's an obvious solution, but right now I can't find it. Any help will be much appreciated!



Edited to add:

Changing the cell format to text, or All, to avoid it being read as a number, does somethign even stranger, eg 4-5 is changed to 39572
 
Joined
Dec 8, 2006
Messages
4,469
I have a table in which the first column is numbers. Sometimes there is a range eg 3-4 in which Calc auto changes it to a date eg 3-4 becomes 03/04/08

I've tried rummaging through its menus but can't find the solution. Changing the date format for a particular cell makes that cell look ok, but I'd have to do that for individual cells becuase copying it to the whole column spoils those cells which contain a single digit, not a range.

I'm sure there's an obvious solution, but right now I can't find it. Any help will be much appreciated!



Edited to add:

Changing the cell format to text, or All, to avoid it being read as a number, does somethign even stranger, eg 4-5 is changed to 39572
Use an apostrophe before the number.
eg. * '4-5 * instead of * 4-5 *
 

cloudburst7

Thread Starter
Joined
Aug 31, 2008
Messages
154
Thank you - that will help when inputting more data or creating new tables in the future.

However I've got a full table worth of data already - is there a quick way to put an apostrophe before all the numbers then? Or must I go through the table manually changing all the affected cells?
 
Joined
Dec 8, 2006
Messages
4,469
Thank you - that will help when inputting more data or creating new tables in the future.

However I've got a full table worth of data already - is there a quick way to put an apostrophe before all the numbers then? Or must I go through the table manually changing all the affected cells?
That I don't know.
 
Joined
Jul 25, 2004
Messages
5,458
If you have Excel, and the ASAP Utilities add-in, there is. Do you have Excel to do this? You could post it up here and someone could do it for you, I'm sure. Wouldn't take very long at all.
 
Joined
Oct 20, 2004
Messages
7,837
Excel does the same thing - it thinks 3-4 is a date, and both programs hold dates as a # of days since 1/1/1900 (I think that is the base date) so that is what the 4-5 becoming 39572 means.
The following directions really are based on Excel, but I am sure they will work in Calc, although the specific commands may be a little different.
For a workaround, first format your cells that are showing the date as Format, Cells, Special. In the bar at the top, enter "m-d;@" without the "" around it.
Your text will now appear as 3-4. However, Calc still thinks it is a date, so you might prefer to go one more step. Copy all that data, open up notepad and paste it in. Notepad sees everything as text, so that is now the way it is formatted. In Calc, format your column (or another if you prefer to be able to compare the results) as text, by highlighting it and using Format Cells, Text.
Copy or cut all the data from Notepad and paste it into the text-formatted column in Calc.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top