Windows 7 - Vista and Excel 2003 date formats

Asked By Lindsay Graham

20-Jan-10 01:01 AM

I am having real problems with Excel 2003 in Vista as dates and times are
treated as text rather than values.  In trying to fix this problem, I
checked the language being used by Excel [the Vista default is English
(Australia)].  In Win XP, I do that by looking in Excel Help > About
Microsoft Office Excel > System Information, then expand Office 2003
Applications > Microsoft Office Excel 2003 > Summary.

But in Vista, when I go (as Administrator) into Excel Help > About
Microsoft Office Excel > System Information, Office 2003 Applications is
not there!  Is this somewhere else in Vista or does this indicate a
problem with the installation of Excel on this computer?  Or is there
some other reason?

Lindsay Graham
Canberra, Australia

20-Jan-10 06:04 AM

This is really an XL problem, and you would be better served on an XL NG.
There seems to be a fault in your XL installation.

Having said that, I found a few years ago that dates on 2003 and earlier
versions of XL do not work as advertised. I'ts a waste of time trying to
manipulate dates using formulae or VBA code, because your code may work one
day for one user, but not the next. Example: try calculating an expiry date
1 year later than today's date.

There is also the problem that XL interprets all sorts of strings as dates,
without asking. The only way out is to define the type of every cell in the
range you are likely to use.

Regards

20-Jan-10 07:08 AM

I do not think it is an Excel problem.  I have used dates and times
extensively in Excel 2003 and Windows XP (and their predecessors).  I
never had any problems until I shifted to Vista.  Date formatted cells
work fine in WinXP but, in the same Excel file, will not work in Vista.

I have been getting some help (although, so far, no solution) through an
Excel mailing list, and that led me to the language issue raised below.
Because that seems to me to be a Vista issue, I asked about it on this
newsgroup.

I hope someone can help.

Lindsay Graham

22-Jan-10 06:53 PM

Lindsey:

As noted, you would  be better off posting to microsoft.public.excel.

I noticed the same issue getting worse and worse in excel and
definitely between office applications (e.g. copy a column from Access
to Excel). The best solution, if you are getting the funky little
green triangles in the corner of the cells and the exclamation point
drop down is to select the cells in error and choose the appropriate
fix from the menu, thereby correcting all cells.

For more specific advice, see the excel newsgroup. They will be
familiar with whatever troubles you, even if it only shows up in
Vista.

22-Jan-10 11:28 PM

Thank you to those who tried to help me with this frustrating problem.
After a lengthy process of trial and error and assistance from a
friend who is more computer-literate than I am, the cause has been found
-- hooray!!

Weird as it sounds, it was the way in which I had set the Vista default
*time* format in Control Panel > Regional and Language Options.  I had
the time format set as HHmm -- although this 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 is set to HH mm
or HH:mm.

In Windows XP, the time format HHmm will not be accepted -- it must be
either HH mm or HH:mm.  What a pity that Vista accepted it -- and caused
me (and others) to waste many many hours trying to solve the problem.

Can others replicate the problem?  If so, it should be reported to
Microsoft as a bug.

Lindsay Graham