Date Time formatting problem

[sgl]

Hi all,
I am using Excel 2003 with Vista Business.
International settings and date/time format set to United States.

I am setting a "custom format" in a cell as dd mmm yy hh:mm. When the cell
is empty the Type Function evaluates to 1 (numeric). I enter a date and time
say 1/1/08 23:45 and this appears in the cell in exactly the same manner. The
Type Function changes automatically and now evaluates to 2 as Text.

If I enter a date without a time say 1/1/08 this appears as 1 Jan 08
00:00:00 (using same format as before dd mmm yy hh:mm). In the formula bar it
appears as 01 01 2008 12:00:00 AM.

I have workbooks developed in Excel 2000 with Windows XP which were working
fine. Now with the same WkBks, but using Excel 2003, all of a sudden nothing
with date/time in the same cell works. All formats have converted to Text.
calculations cannot be performed.

Am I missing something here which has changed between systems??
Thanks in advance/sgl

 

[joel]

Did you save the file as CSV? CSF doesn't support formating or formulas. A
CSV file is really a text file that you can view with a text ditor like
notepad.

 

[sgl]

No this is a straight .xls file. I have opened an old xls workbook where a
template had been developed in Excel 2000 and tried to enter data in the
date/time formatted cells and this problem came to light.

Everything was converting to text either when I enetered data in blank new
cells or altering already completed cells.

I then tested this in a brand new workbook formatting new cells as I have
explained and the same thing happened.

Totally confused I am!!!!

 

[joel]

I need more info.

1) When did the problem start occuring. After Vista was installed.
2) Does the problem happen with more than one PC?
3) Verify you are still running with Excel 2003 using menu Help Info
4) Try opening Excel from Start Menu. does same thing happen?
5) check where the excel short cut is getting the executable. right click
shortcut and check properties
6) Do a seach on your C: drive for exce.exe and see if you have more then
one verion of the exe. Try opening each version ans see if the same thing
happens
7) Make sure tthe extension of the file is XLS.
8) Make sure there arre no macros in your XLSTART directory that is causing
the problem.


these are some quick items to check to help locate the problem. I checked
the Tools - Options for some setting that may explain your problems but
didn't find any.

 

[sgl]

Here goes,

1 - This is a new machine Lenovo T500 which came with Vista Business
preloaded. Also Office 2007 was preloaded. I uninstalled this as I was
developing in Excel 2003 and there were conflicts between the two versions
especially with Outlook. Whether all files have been deleted from system
cannot check. I get a lot of crashes with Excel 2003/Vista which I do not get
using Windows XP Pro.
2 - Need to check this as but do not have another PC available
3 - Checked this is certainly an Excel 2003 version
4 - Same problem as before
5 - Excel shortcut leads to MS Office Excel 2003
6 - Only one version there
7 - Extension is .xls
8 - Only files in this folder are Personal.xls - checked that and nothing
there

thank you/sgl

 

[sgl]

Just checked on a Windows XP Pro with Excel 2003 and it works fine. So seems
that the common link is Vista. The question is how do I solve this???

 

[Niek Otten]

Adjust your date format in Windows (Control Panel). Excel recognizes dates
that Windows recognizes.

 

[sgl]

Nick I have already tried that. My Regional Settings are as follows:

Formats English (United States)
Short date dd mmm yy
Long date dd mmmm yy
Time HH:mm:ss (24 hours)

I have the same settings on a computer with Office 2003 and Windows XP Pro
where calculations perform correctly. With Vista Business and Excel 2003 all
entries made in a Custom setting of dd mm yy HH:mm convert to Text once you
enter time, say 1 Jan 08 23:45, which makes calculations impossible!

sgl