Incorrect Date Format

[John Taylor]

Hello to all,

The situation:

1. Excel 97 SR-2 and Windows 98SE, Short Date Format dd/MM/yyyy

I wrote a macro that imports dates (dd/mm/yyyy) and stock prices from an
Access database, then adds some formulas at the bottom of the data to
calculate highest and lowest prices and the corresponding dates.

Works fine, and the formulas correctly return the price and date (in
dd/MM/yyyy format, without needing to format the cells).

2. Excel 97 SR-2 and Windows XP Pro SP1, Short Date Format dd/MM/yyyy

The macro created in 1. above works fine under these conditions, but the
formulas that return the dates return them in the wrong format. Instead of
being automatically formatted as dd/MM/yyyy, they appear in m/d/yy format.

Does anyone have an explanation, or more importantly, a way that I can
permanently overcome this problem?

In both cases the dates are imported into Excel in the correct (dd/MM/yyyy)
format, and as 'real' dates and not text; that is, I can perform maths
functions on them.

I realise I could add a bit to the macro to format the relevant cells as
required, but that would just be masking the underlying problem, which could
then present itself later in other workbooks.

TIA

Cheers,

John

 

[Frank Kabel]

Hi
a simple solution: In your macro simply set the format according to
your needs.

 

[Amedee Van Gasse]

John Taylor wrote:

*snip* date problem

Does anyone have an explanation, or more importantly, a way that I can
permanently overcome this problem?

In both cases the dates are imported into Excel in the correct
(dd/MM/yyyy) format, and as 'real' dates and not text; that is, I can
perform maths functions on them.

I realise I could add a bit to the macro to format the relevant cells
as required, but that would just be masking the underlying problem,
which could then present itself later in other workbooks.

TIA

Cheers,

John

John,

The solution would be that you would change the macro to format where
needed, but you already say that yourself.

An explanation could be that Excel and Windows are made in the USA, and
those leftponders don't know how to write dates properly like the rest
of the world does. Like now they have a film in the cinemas about 9
november...

PS: I call them leftponders, but you my dear antipode, what do you call
them?
PPS: Well you aussies are not really my antipodes, that would be the
kiwis. ;-)