To review, all dates entered into EXCEL97 on my DELL computer insist on
displaying the year in YYYY format unless subsequently manually
reformatted. Dates enterred into EXCEL97 on my IBM computer, act as
expected. Both machines are running WinXP-Pro SR2. The software on both
machines appears to be identical except:
DELL indicates
" Sfwr Abstraction Layer = "5.1.2600.2705(xpsp.050622-1524)"
IBM indicates
" Sfwr Abstraction Layer ' "5.1.2600.2180(xpsp_Sp2_rtm o4o8o3-2158 "
I've done two things:
(1) I replaced all 77 MByte of C:\Program Files\Microsoft Office\Office
directory files on my DELL computer with the identical directory files
from IBM computer which does not have the Excel date problem. I also
replaced all C:\Program Files\OfficeUpdate11 files from the IBM computer
with does not have the Excell date problem. I also unsuccessfully
sought to install the SR-2 Update, [sr2bof97.exe]. When I open Excel on
DELL, it now indicates SR 2(l) version, rather than SR 1, but it does
not simply display "SR 2" as on the IBM machine. I realize this move
could create problems in that Office files may no longer agree with
files anticipated by the registry, but so far I've found no additional
problems. The bottom line is that the date formating problem has not
changed. No mater how I format a cell, and no matter how the date is
entered, it always appears with a yyyy format...
But.... The format settings for the column for the month and day are
observed. Thus if the column formats are set to mmm/d/yy, a date such as
1/3/05 will be immediately shown as Jan/3/2005.
(2) I change the default windows Time and date setting to be of the form
mmm/dd/yy. The change did not impact how excel displayed dates.
(3) When I try to install pstvh sr2bof97.exe , it says it can't install,
possibly because of incompatibility. I had the same problem before I
copied over the files. I'm able to install path xl8p10pkg.exe which has
most of the same updates.
(4) I've rebooted my machine from a cold boot to make sure changes were
reflected in how the program was loaded.
I have hesitate to totally remove MSoffice and reinstall, because
undoubtedly there are customizations in Word and Powerpoint that I'll
loose. My Office install disc. does not seem to give me the option to
just replace portions of OFFICE, although I seem to recall that feature.
Again... this is a puzzlement, and suggestions will be welcomed.
Dave said:
I don't think that xl97 had the note at the bottom. I don't recall if that
version marked the samples that are picked up from the windows regional
settings.
But it would be simple for you to test. Try changing that windows regional
setting and see what happens to the way the date is displayed.
windsurferLA wrote:
The is no asterisk in front the 04/05/97 sample date in the
Format | cells | number | date where date is an option under number.
If I move the file to my other machine, the cell formating works as
expected. Independent of how the date is entered, the date is formatted
in accordance to the date formating of the cell.
I have noticed that the machine with the problem is shown as running
XL97 w/SR-1, while the machine that works right shows XL97 w/SR-2.
I need to see about an SR-2 update to Excel on my problem machine.
WindsurferLA
Dave Peterson wrote:
Saved from a previous post.
When you do Format|cells|Number tab and look at that cell's format, I'm betting
you see something like:
*03/14/2001
That asterisk means that you chose a short date format that is picked up from
the Windows Regional settings (Date tab).
xl2002 is more honest with the way it deals with dates.
At the bottom of that dialog (xl2002):
Date formats display date and time serial numbers as date values. Except for
items that have an asterisk (*), applied formats do not switch date orders with
the operating system.
Since yours is marked with an asterisk, your date will switch formats with the
setting in the OS.
=====
So if format your date using one of the formats marked with an asterisk, then
excel will use that pc's windows date format setting. So if you open the file
on a different pc that uses a different windows date format setting, then you'll
see something different.
This was what Toni.Gee tried to explain--excel uses a windows setting to know
how to display those formats that are marked with an asterisk.
windsurferLA wrote:
I'm nearly certain that the cellS are not formatted as text. If I use
the contents of the cell in a formula such as z12 = C12 * 1, where the
date is entered into cell C12, the contents of Z12 is a number like
36,000 or so, the date number.
I do not encounter the problem on my other machine that is also running
Office 97. I think machines were loaded with the same copy of office,
although I have two separate legitimate licenses for Office97.
I encounter the same problem on brand new workbooks opened on the same
machine.
SUGGESTIONS ARE STILL WELCOME. I'm going to try to move the file to the
other machine, and see if I have the same problem when the file is
opened with another copy of the software.
WindsurferLA
Lori wrote:
It's likely that cells are formatted as text.
Data>Text to Columns>Finish on the column should take care of it.
windsurferLA wrote:
I'm using Office97. This problem has not been an issue until recently.
In the past (unless I'm crazy), when I formatted a cell, perhaps in the
format MM/DD/YY, the value was always displayed in that format even if
you entered new values in the format mm/d/yyyy, or any other format.
Now, on all my new spreadsheets, when I enter a date, the format changes
to mm/dd/yyyy independent of the prior setting. I assume I changed some
default format setting somewhere. Can someone tell me what I may have
changed?
