Hi,
We have an application (Excel Add-in) that writes dates to Excel
according to the current Short Date format. What I've found in Excel
2003 (and not previous versions, although I haven't tried Excel XP) is
that "ambiguous" dates, i.e. 01/11/04 are defaulting to appearing in
US mm/dd/yy format and not the UK format. I can boil the problem down
to the following - I don't have to write a line of VBA to demonstrate
this:

* Format a cell as custom format dd/mm/yy hh:mm:ss
* Type in "1/11/04 10:00:00" - i.e. 1st November
* Appearing on the cell is 11/1/04 10:00:00

By putting a quote in front of the value in my application I can force
the dates to appear correctly - but this takes away from the
flexibility of my application.

Note Regional settings are ALL UK/Ireland. This has been checked and
double checked.

Is there some hidden setting in Excel 2003 that needs to change so
that it recognises the formatting imposed on it? I read on someones
post that VBA and Excel were writting by different groups. I'll need
some convincing that this is not a bug, but more importantly what can
I do about this (and tell my customers, because they probably won't
believe me when I say this is an Excel bug that I can't work around).

Suggestions very welcome!
thanks Oisin

Hi Oisin

using your example (in Australia where we have the same default date format
as you) i had 1/11/04 10:00:00 appear in my cell. i'm using excel 2003
running on windows xp home.

The only date settings i ever played with were under control panel /
regional settings ...

sorry i can't be more helpful, but it's working for me.

Cheers
JulieD

"Oisin O'Reilly" wrote in message
om...
Hi,
We have an application (Excel Add-in) that writes dates to Excel
according to the current Short Date format. What I've found in Excel
2003 (and not previous versions, although I haven't tried Excel XP) is
that "ambiguous" dates, i.e. 01/11/04 are defaulting to appearing in
US mm/dd/yy format and not the UK format. I can boil the problem down
to the following - I don't have to write a line of VBA to demonstrate
this:

* Format a cell as custom format dd/mm/yy hh:mm:ss
* Type in "1/11/04 10:00:00" - i.e. 1st November
* Appearing on the cell is 11/1/04 10:00:00

By putting a quote in front of the value in my application I can force
the dates to appear correctly - but this takes away from the
flexibility of my application.

Note Regional settings are ALL UK/Ireland. This has been checked and
double checked.

Is there some hidden setting in Excel 2003 that needs to change so
that it recognises the formatting imposed on it? I read on someones
post that VBA and Excel were writting by different groups. I'll need
some convincing that this is not a bug, but more importantly what can
I do about this (and tell my customers, because they probably won't
believe me when I say this is an Excel bug that I can't work around).

Suggestions very welcome!
thanks Oisin

Hi Oisin
The books by Green et al "Excel 2002 VBA" or earlier/later editions
cover international issues (i.e. dates) very thoroughly, and I'd
recommend looking at that.
I think the date setting format default is part of the preferences set
in Control Panel, so depends on the machine and its user, not Excel.
I tend to avoid short date format altogether and output date values
like

format(DateValue(cell.value), "dd mmm yyyy")

DateValue takes the given date in cell.value and represents it in
Excel's internal date format. Format then shows it as something
readable.

regards
Paul

(Oisin O'Reilly) wrote in message . com...
Hi,
We have an application (Excel Add-in) that writes dates to Excel
according to the current Short Date format. What I've found in Excel
2003 (and not previous versions, although I haven't tried Excel XP) is
that "ambiguous" dates, i.e. 01/11/04 are defaulting to appearing in
US mm/dd/yy format and not the UK format. I can boil the problem down
to the following - I don't have to write a line of VBA to demonstrate
this:

* Format a cell as custom format dd/mm/yy hh:mm:ss
* Type in "1/11/04 10:00:00" - i.e. 1st November
* Appearing on the cell is 11/1/04 10:00:00

By putting a quote in front of the value in my application I can force
the dates to appear correctly - but this takes away from the
flexibility of my application.

Note Regional settings are ALL UK/Ireland. This has been checked and
double checked.

Is there some hidden setting in Excel 2003 that needs to change so
that it recognises the formatting imposed on it? I read on someones
post that VBA and Excel were writting by different groups. I'll need
some convincing that this is not a bug, but more importantly what can
I do about this (and tell my customers, because they probably won't
believe me when I say this is an Excel bug that I can't work around).

Suggestions very welcome!
thanks Oisin

Right on the money Ron!!!!
The October dates were not being recognised as dates in Excel
(although the VBA recognised them as dates. The solution appears to be
to cast the values as doubles, then let the cell formatting do the
rest. A tell tale sign was also differing alignment (text alignment
for the October dates) between the correct and incorrect dates - and
of course the most perverse thing is that the dates appearing
correctly were the ones not interpreted correctly as dates.

There is definately something fishy going on here between VBA/Excel
and Excel itself but I have my workaround.

I post questions on newsgroups very rarely - when I do it's because
I'm desperate, so I'm very grateful to all you guys.
Oisin

On 23 Nov 2004 14:04:28 -0800, (Oisin O'Reilly) wrote:

Right on the money Ron!!!!
The October dates were not being recognised as dates in Excel
(although the VBA recognised them as dates. The solution appears to be
to cast the values as doubles, then let the cell formatting do the
rest. A tell tale sign was also differing alignment (text alignment
for the October dates) between the correct and incorrect dates - and
of course the most perverse thing is that the dates appearing
correctly were the ones not interpreted correctly as dates.

There is definately something fishy going on here between VBA/Excel
and Excel itself but I have my workaround.

I post questions on newsgroups very rarely - when I do it's because
I'm desperate, so I'm very grateful to all you guys.
Oisin

I'm glad I was able to suggest something to you that helped you solve your
problem.

Especially with regard to dates, it's helpful to keep in mind that VBA and
Excel are two different programs. Even the allowable date ranges are
different.


--ron