Worksheet Automatically Reverting Data Format

Question

Worksheet Automatically Reverting Data Format

Good Afternoon All,

I'm having an odd issue, I have a spreadsheet I use to keep track of hours spent on working on client's website, for billing purposes.

You can see below that I've changed all the lines in the table to "Number" format with two decimals so the autosum functions work.

Here's the kicker: If you actually click on an individual cell, or save an reopen, anything like that...It reverts some (not all) of the cells to text format, and some to duration format...I have no idea why this change wont stick, and I need some help! It's seriously driving me insane! 😠

As you can see with just this one cell selected, it's reverted to "Text" format.

Help! 😟

MacBook Air (13-inch Mid 2013), OS X Mavericks (10.9)

Posted on Nov 5, 2013 10:24 AM

Reply

Answer 1

Nov 5, 2013 10:55 AM in response to CleverMistakes

Clever,

Rather than selecting the entire table object, try selecting all the cells when you make the Number Format selection. By that I mean select A1 and Shift-Click cell I12.then format. I'm taking a shotgun approach because there's not enough in the way of clues for me to guess a more specific cause.

Jerry

Reply

Answer 2

Nov 5, 2013 11:00 AM in response to Jerrold Green1

Jerrold Green1 wrote:

Clever,

Rather than selecting the entire table object, try selecting all the cells when you make the Number Format selection. By that I mean select A1 and Shift-Click cell I12.then format. I'm taking a shotgun approach because there's not enough in the way of clues for me to guess a more specific cause.

Jerry

Just tried your suggestion Jerry, unfortunately I have the same result. Is there additional information I could provide that might be helpful? I could even provide a link to the whole file if that would help. I just thought the images might give a better idea of what I'm saying.

Every time I change the format to Number, some but not all the cells are right back to "Text" or "Duration" when I click on them.

Reply

Answer 3

Nov 5, 2013 11:09 AM in response to CleverMistakes

If you post a link, I'll take a look-see.

Jerry

Reply

Answer 4

Nov 5, 2013 11:22 AM in response to Jerrold Green1

https://www.dropbox.com/sh/drn750cg2nug7jv/lZ7lPJCYth

Here's a link to the file, any help you can provide would be appreciated! It's driving me bonkers! My business partner and I are so confused as to why it really doesn't want to work! 😟

Reply

Answer 5

Nov 5, 2013 11:53 AM in response to CleverMistakes

Clever,

I don't know how much comfort you will get from this, except as a way forward, but without a specific cause of the earlier troubles.

I couldn't fix your document. Not even by copying it and pasting into a fresh blank template, which usually fixes this type of problem. I was able to start completely fresh and create an equivalent document that has no troubles whatsoever, so far. It looks like this...

I'll send it to you if you like.

Jerry

Reply

Answer 6

SGIII

Level 8

35,276 points

Nov 5, 2013 12:09 PM in response to Jerrold Green1

Jerry,

What worked on my machine is select B2:H11 of the original sheet and apply your "delete" trick: with the cells in the middle selected just hit the delete key to clear the contents and formatting. Then I'm able to reenter the values and the number format "sticks".

SG

Reply

Answer 7

Nov 5, 2013 12:13 PM in response to SGIII

SG,

I thought I tried that. Maybe I should try again, but once a document is possessed, I sometimes feel better just getting away from it.

Jerry

Reply

Answer 8

Nov 5, 2013 12:22 PM in response to SGIII

SGIII wrote:

Jerry,

What worked on my machine is select B2:H11 of the original sheet and apply your "delete" trick: with the cells in the middle selected just hit the delete key to clear the contents and formatting. Then I'm able to reenter the values and the number format "sticks".

SG

Hey this worked! So on the original document all I did was screenshot my data so I knew my times worked and jus mashed the delete key, and reset the format! Hallejuah!

Thanks so much for your help, both of ya!

Reply

Answer 9

SGIII

Level 8

35,276 points

Nov 5, 2013 12:30 PM in response to Jerrold Green1

Jerry,

I agree about that possessed business. Something funky going on with cell Data Rormats in Numbers 3.0, often (it seems) with legacy Numbers 2.3 documents. Sometimes better to exorcise the demons and start afresh.

@CleverMistakes, just in case the demons reappear it might be good idea to keep an extra backup or two!

SG

Reply

Answer 10

Nov 5, 2013 2:05 PM in response to CleverMistakes

CM,

Here's the version I created to step around the problem. iCloud Numbers LInk

I'll leave it up on the cloud for a week or so.

You might want to take a look at the expressions. They are simple because all the calculations are now ensconced in Header Cells.

It's a layout I've come to like over time. The biggest difference is that row sums have to be on the left, and I now am comfortable with that, even prefer it.

Jerry

Reply

Answer 11

SGIII

Level 8

35,276 points

Nov 5, 2013 2:26 PM in response to Jerrold Green1

The link works well. Just in case it's not obvious, this can be download as a Numbers 3 document:

I remember thinking it would be nice to be able to have a Header column on the right.

SG

Reply