Date formats in Excel 2003 under Vista

[Lindsay Graham]

For a long time, I've had an extremely frustrating problem in Excel 2003
on a Vista computer. Several web searches have located no references to
this problem, and many things have been tried without success.

Simply put, dates and times do not format as dates and times -- they
remain in text format regardless of any Date or Time format that is
applied. There are 2 Win XP computers on the same home network, and
date and time formats work perfectly in Excel files on those computers.
This different XP/Vista behaviour applies even when the *same* Excel
files are used.

Has anyone else heard of this happening? Or does anyone have any ideas
for solutions?

By way of background:
1. The Control Panel > Regional settings on all computers are Language
= English (Australia), Short Date format = d/M/yy.
2. On all computers, Excel 2003 is from Office 2003 Professional and is
completely up-to-date (including SP3).
3. I uninstalled/re-installed Office 2003 on the Vista computer, and
still the problem persists.
4. Dates entered as, eg, 22/1/10, are always returned as text,
regardless of whether the cell is unformatted or date formatted (I've
tried many different date formats in Excel) before or after the date is
entered.

Most of my remaining hair has been torn out trying to resolve this -- if
I can't get there soon, I'll have to switch to OpenOffice.

Lindsay Graham
Canberra, Australia

 

[John]

Hi Lindsay
What do you get on a new worksheet if you press and hold Ctrl ;
I'm in Canada and with custom formating I don't get the same thing if I type
22/1/2019 and press Ctrl ; .
22/1/2010 is text the other is a date.
Try it
regards
John

 

[Bernard Liengme]

This must be most frustration. Please note it has nothing to do with
Vista/Excel 2003 combo. I ran this (together with Excel 2007) for several
years before switch to Win 7.

When a file made on the Vista PC is opened on another PC, the date is OK or
still text?
If it is text, send me such a file (get my email fro my website)

Have you tried to repair (not reinstall) Excel from the Control panel.

To save a few strand of hair and as a last resort, remove Excel 2003 and
download the free Excel 2010 beta.
If this works, saves your pennies until the final 2010 release. And
configure it to save in Excel 2003 format so you can share with the other
PCs. Note you must remove Office 2003 before installing the beta.
best wishes

 

[Lindsay Graham]

Thank you for the replies, folks. After a process of trial and error
and assistance from a friend who is more computer-literate than I am,
the cause has been found.

Weird as it sounds, it was the way in which I had set the default time
format in Control Panel > Regional and Language Options. I had the time
format set as HHmm -- although this format was accepted by Vista, it
caused (for reasons that are totally inexplicable to me) dates entered
in Excel 2003 to be corrupt or not accepted as dates. It appears that
the problem does not occur when the default time format set as HH mm or
HH:mm.

In Windows XP, the time format HHmm will not be accepted -- it has to be
either HH mm or HH:mm.

I'd be interested to hear whether others can replicate the problem -- if
so, it ought to be reported to Microsoft as a bug.

Lindsay Graham

 

[Fred Smith]

You have found a Vista bug.

I have Vista with Excel 2007. I changed my time format to HHmm, and all date
entries in Excel were taken as text. I changed it back to HH:mm:ss, and
Excel started accepting date entries properly.

I'm impressed you were able to track this down.

It looks like the problem was fixed in Windows 7, because I also tied "HHmm"
under Windows 7 and Excel 2007, and date entry worked properly.

Fred

 

[Bernard Liengme]

Well done!

Thank you for the replies, folks. After a process of trial and error and
assistance from a friend who is more computer-literate than I am, the
cause has been found.

Weird as it sounds, it was the way in which I had set the default time
format in Control Panel > Regional and Language Options. I had the time
format set as HHmm -- although this format was accepted by Vista, it
caused (for reasons that are totally inexplicable to me) dates entered in
Excel 2003 to be corrupt or not accepted as dates. It appears that the
problem does not occur when the default time format set as HH mm or HH:mm.

In Windows XP, the time format HHmm will not be accepted -- it has to be
either HH mm or HH:mm.

I'd be interested to hear whether others can replicate the problem -- if
so, it ought to be reported to Microsoft as a bug.

Lindsay Graham