incorrect date conversion in excel 2003

[- - - Foundation - - -]

hi,
consider the following date problem, i have 2 programs. one that
ceates test results using a date field. this is exported to excel
2002, but in excel 2003 strange things happen. the dates in 'test' and
2002 are correct

test 2002 2003
DD-MM-YYYY DD-MM-YYYY
01-06-1998 01-06-1998 06-01-1998*
10-11-1998 10-11-1998 11-10-1998*
25-12-1998 25-12-1998 25-12-1998

As you can see the *dates in 2003 are in MM-DD-YY format, while the
last date in 2003 is correct.

What is the problem here, help please.

Joske

 

[Frank Kabel]

Hi
question is HOW do you export nthe dates to Excel.

 

[Ron Rosenfeld]

hi,
consider the following date problem, i have 2 programs. one that
ceates test results using a date field. this is exported to excel
2002, but in excel 2003 strange things happen. the dates in 'test' and
2002 are correct

test 2002 2003
DD-MM-YYYY DD-MM-YYYY
01-06-1998 01-06-1998 06-01-1998*
10-11-1998 10-11-1998 11-10-1998*
25-12-1998 25-12-1998 25-12-1998

As you can see the *dates in 2003 are in MM-DD-YY format, while the
last date in 2003 is correct.

What is the problem here, help please.

Joske

One way of getting the results you see would be if your regional settings:
Start/Control Panel/Regional and Language Options are set to English(United
States) and your date formatting in Excel is set to Dutch(Netherlands).

Date entry is parsed according to the Regional and Language options, and the
display is according to your format settings in Excel.

So if your Regional Settings are US-centric, then the input of 01-06-1998 gets
translated to 6 Jan 1998; 10-11-1998 becomes 11 Oct 1998; and 25-12-1998 is not
translatable, so gets entered into Excel as a text string.




--ron

 

[- - - Foundation - - -]

Hi Ron,
You are right. I changed the settings to MM-dd-yyyy en everything was
fine.
Except that I'm dutch, the regional settings are dutch (except for the
date now) and the excel2003 version that I use is dutch.

Why can I not change these settings in excel itself, I couldnt find it
;-)

groetJos

 

[Ron Rosenfeld]

Hi Ron,
You are right. I changed the settings to MM-dd-yyyy en everything was
fine.
Except that I'm dutch, the regional settings are dutch (except for the
date now) and the excel2003 version that I use is dutch.

Why can I not change these settings in excel itself, I couldnt find it
;-)

groetJos

I'm glad you have things working.

I do not understand your question, even though your English is much better than
my Dutch <g>.

But the important thing to keep in mind is that the interpretation of Date
INPUT is based on Control Panel/Regional Settings.

How the date is displayed is determined by the cell formatting in Excel.

Although there are VBA solutions one can use, there is no way within Excel
itself that you can change how a date that you input is interpreted -- that can
only be done in the Regional Settings.


--ron