Excel 2010 Time Format & Re-Formatting

Question

1. Cell A1
2. Format A1 as General
3. Enter 0.33333
4. Format A1 Time-24 hrs
5. Return 08:00:00

When I double-click the cell it becomes 8:00:00 AM.

When I re-format A1 into any WHATEVER OTHER format(s), it still retain the same value (8:00:00 AM) i.e the value does not budge!

Answer 1

Boyluv2000 wrote:

1. Cell A1
2. Format A1 as General
3. Enter 0.33333
4. Format A1 Time-24 hrs
5. Return 08:00:00

When I double-click the cell it becomes 8:00:00 AM.

When I re-format A1 into any WHATEVER OTHER format(s), it still retain the same value (8:00:00 AM) i.e the value does not budge!

It sounds like you have the option "Edit directly in cell" set, which I believe is the default.  I usually disable the option for a number of reasons.  (Click on File, Option, Advanced.)

When you double-click, you see a cursor in the cell, and you are in edit mode.  At that point, the cell contents are displayed according to the rules for the Formula Bar.  Those display rules are different than the cell format, although they are influenced by the cell format.

After you double-click, if you simply press Enter or select another cell, the cell should be displayed according to its format again.

There is nothing we can do to change the display rules for the Formula Bar -- and after double-clicking a cell when the option "Edit directly in cell" is set.

And unfortunately, there are circumstances when that screws us up, since pressing Enter will effectively (re)enter what the Formula Bar displays.

For example, if we format the cell as Custom hh:mm:ss.000 and enter 12:34:56.789, the Formula Bar (and double-click when "Edit directly in cell" is set) will display 12:34:57 PM, and pressing Enter or selecting another cell will cause the cell value to become 12:34:57, albeit still displayed in the format 12:34:57.000.

Answer 2

Oh dear me, my English is so simple to read and understand.

After Step 5, why does double-clicking cell A1 produce that kind of behavior as I descibed in detail in the original post.

After Step 5, IF I DO NOT DOUBLE-CLICK CELL A1, YES, I CAN RE-FORMAT THE CELL IN OTHER FORMATS.

Please do not muddled things when a user is trying to explain something so simple to grasp!

TQ!

Answer 3

After changing the cell format try pressing the F2 function key and pressing Enter, does that do anything?  Also check exactly what is entered in the formula bar in case you've accidentally entered a leading apostrophe or something.

Answer 4

Dear Jeeped,

After step 5, did you double-click the cell?

Answer 5

I'm not sure how the procedure I'm using differs from yours, but I cannot reproduce this behavior. After setting the 0.33333 to a 08:00:00 AM format, I can easily change it again to General, Number, Accounting or even a Custom format and the underlying value becomes 0.33333. I have also tried this with and without in-cell editing.