Auto date changing in Excel is maddening

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using MS Office Excel 2003 (11.6113.5703) to post data that spans over
several days.
sample plot
http://peswiki.com/index.php/Direct...ta:Exp10.4_Different_Standing_Discharge_Rates
or try http://tinyurl.com/6f8zn

We had to jump through all kinds of work-arounds in order to accomplish
this. It was not a straight-forward process, though it should have been.

When I enter the date format, for example, as
Format > Time > 3/14/01 13:30
And then enter 11/17/04 0:30:30, it enters okay, but when I double click on
the cell to edit it, Excel changes it to read 11/17/2004 12:30:30 PM which is
both wrong and in a format that will not post in our chart, so I have to
manually delete out the "20" from 2004 that Excel added in, against my
wishes, as well as the " PM" and I have to change the 12 back to a zero.

I promise you I'm looking for another program to accomplish my data
purposes. Excel sucks. Microsoft has a way of trying to hold people by the
hand, giving them what MS thinks they want, and not giving them a way to
override MS. I hate that about MS. WordPerfect is such a superior product,
yet I can't use it on my system because MS incompatibilities cause my
computer to freeze out WP.

MS is the biggest and the monopoly, but they are not the best. You have a
lot of very disenchanged users who only use MS because it is what is
ubiquitous, but not because it is the best.

You should be ashamed of yourselves. Yes, you've made a wad of cash, but
you have not served the public as you should have.

Sterling D. Allan
Executive Director, PES Network Inc
http://pureenergysystems.com
 
Excel's display is governed by the Regional Setting in the "Start->Settings->Control
Panel"
I find this a pain in the butt, but that's the way it is.

(Also, this isn't a ng that is run by MS.)
 
I can't reproduce what you're seeing.

First, Format is not tied to what's stored in the cell. When you enter
11/17/2004 0:30:30, the value 38308.0211805556 is stored in your cell.
How it's displayed depends on your number format, but changing number
format doesn't change the value.

When you double click in the cell, the Formatting bar, or the cell if
you have the Edit in Cell option set, display whatever your regional
settings are set to. Does it really show 12:30:20 PM, not AM? If so,
something's hosed up. But you *don't* have to edit the "20" out - just
hit return and the number format will show only the last two digits.

Don't bother bitching to or about MS here - nobody who reads and
responds works for MS. We're all just users, and in many cases we agree,
but other than venting, your diatribe largely is shouting into the
vacuum.
 
What was not straightforward about plotting your data? Date-time in one column,
values in the next, run the chart wizard. Your chart is not unique.

J.E. has covered number formats as well as I would have.

Last time I used WordPerfect (admittedly a few years back), I felt it was a
PITA/POS, but that's my humble opinion. Problems with it not working in Windows are
more likely due to an improper installation than to any malfeasance on Microsoft's
part. Did you remember to unplug the computer from the network, turn off your
firewall and antivirus programs (particularly Norton, which hoses all kinds of other
applications), and close other programs before installing it? Also, I hear
OpenOffice is free.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
You are not alone. I have suffered this maddening behavior for years
through many versions of Excel. I set the formatting for my tim
values, but when I edit the value and hit return, the formattin
reverts to some long format. For years I have been pasting format fro
another cell to get the formatting back.

There is no earthly reason why Excel should change the formatting tha
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 th
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". Th
date is unneccessary clutter in my application and would force me t
use stupid wide columns.

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


-
brhick
 
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.
 
Yes, I am using custom formatting. The custom format I use in thes
cells is h:mm But if you edit the value, and tab out of the cell, th
formatting reverts to some long, standard format. It seems like a bu
to me


-
brhick
 
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/
_______
 
Back
Top