Numbers changing fields to "0"

Question

Level 1

10 points

Numbers changing fields to "0"

Hello! OS 10.14.6:

I have a basic spreadsheet in Numbers that I am using for securities trading records, with various dates, words and dollar amounts in different fields. However, when I enter an assortment of dates, such as "12/19,21,22", as soon as I leave the field, Numbers changes it to just a "0." This is incredibly frustrating. (Only way around it is to add the word "or" or "and" between any of the numbers, but that takes up more room/time than necessary.)

What am I doing wrong and/or how can I get around this?

Thanks very much, community!

iMac, OS X 10.10

Posted on Dec 28, 2021 12:21 AM

Reply

Answer 1

Top-ranking reply

Yellowbox

Level 7

20,981 points

Dec 28, 2021 1:33 AM in response to Bru-Hed

Hi Bru-Hed,

What do you want to achieve by putting 3 dates ("12/19,21,22") in one cell?

The slash (/) tells Numbers that it is a fraction. I don't know what Numbers makes of the commas.

The value (as a decimal number) is 0.0000624...

Format the cells as text before you type or paste the dates.

Regards,

Ian.

Reply

Answer 2

Badunit

Level 7

28,000 points

Dec 28, 2021 1:47 AM in response to Bru-Hed

The actual value in the cell is 6.246E-05. Numbers is interpreting your input as the fraction 12/192122 and doing the math for you.

Your entries are not "dates" as defined by Numbers, they are text. Pre-format the columns as text and you can enter whatever you want without Numbers interpreting it as anything but text.

For singular entries in cells not pre-formatted as text, you can preface the entry with an apostrophe like '12/19,21,22 and it will treat the entry as text. The apostrophe will be removed.

Reply

Answer 3

Bru-Hed Author

Level 1

10 points

Dec 29, 2021 2:48 PM in response to Yellowbox

Thanks, Ian!

I am using a series of dates. In my example, it would stand for December 19th, 21st or 22nd. I thought the cells were agnostic--some of them have text and some numbers so I figured I could put in whatever I want without having them automatically altered, as I do in other software. (Obviously I am naive.)

Best,

Mike

Reply

Answer 4

Bru-Hed Author

Level 1

10 points

Dec 29, 2021 2:47 PM in response to Badunit

Thanks very much for the work-around! How do I "pre-format" the cells as text? The following are all I see when I right-click the cell and when I click on the "Format" menu with the cell highlighted.

Reply