Auto date changing in Excel is maddening
 

You are not alone. I have suffered this maddening behavior for years,
through many versions of Excel. I set the formatting for my time
values, but when I edit the value and hit return, the formatting
reverts to some long format. For years I have been pasting format from
another cell to get the formatting back.

There is no earthly reason why Excel should change the formatting that
you explicitly applied to a cell when you edit the value in that cell.
But that is what it does.

No, the formatting in Control Panel -- Regional Options is not the
formatting it reverts to. I checked. All I want is time: e.g.,
"12:15" and what it gives me after I edit is "12/2/2004 12:15". The
date is unneccessary clutter in my application and would force me to
use stupid wide columns.

So here's another user who would be overjoyed if someone found a
solution!!
- Brian



--
brhicks
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message1238335.html

Did you try using a custom format m/d/yy hh:mm:ss, or just hh:mm:ss for
the person who just wants the time? I have found that to be the best
way to get consistancy.



--
darwinb
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message1238335.html

Yes, I am using custom formatting. The custom format I use in these
cells is h:mm But if you edit the value, and tab out of the cell, the
formatting reverts to some long, standard format. It seems like a bug
to me.



--
brhicks
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message1238335.html

The format is not "in" the cells with the value. The display format is independent
of how you enter the data or how it is displayed in the formula bar, after you've
initially entered a value and Excel has applied its initial default formatting.

When you format a cell, one of the tabs in the Format dialog is Number. Use one of
the built in time formats, or design your own Custom time format. Otherwise, you're
allowing Excel to guess what's your favorite format, based on how it interprets what
you've entered.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

brhicks wrote:

Yes, I am using custom formatting. The custom format I use in these
cells is h:mm But if you edit the value, and tab out of the cell, the
formatting reverts to some long, standard format. It seems like a bug
to me.



--
brhicks
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message1238335.html