1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by cloudburst7, Oct 3, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. cloudburst7

    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
     
  2. RootbeaR

    RootbeaR

    Joined:
    Dec 8, 2006
    Messages:
    4,469
    Use an apostrophe before the number.
    eg. * '4-5 * instead of * 4-5 *
     
  3. cloudburst7

    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?
     
  4. RootbeaR

    RootbeaR

    Joined:
    Dec 8, 2006
    Messages:
    4,469
    That I don't know.
     
  5. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  6. slurpee55

    slurpee55

    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.
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/755840

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice