Date format on cells defaulting to US instead of UK

[Oisin O'Reilly]

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

 

[JulieD]

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

 

[Paul Robinson]

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]

Thanks Julie and Paul,

I have found in fact that this problem only happens once I have run
part of my application - it doesn't happen if I start Excel and try
the test immediately (thanks for that Julie!). However once I do
populate some cells and perform the test on some other cells I get
this behaviour. What I need to do is to get this problem down to first
principles. What I can say for certain is that Excel behaviour has
changed between Excel 2000 and 2003 (Paul I'm quite happy to accept
that there are not internationalisation settings in Excel, I wouldn't
expect there to be). What I am observing once I copy an array of
variant dates to a range in Excel is the following:

31/10/04 23:40
31/10/04 23:50
1/11/04 00:00 <==================== Get this - changed to US format
1/11/04 00:10
1/11/04 00:20

Now all these cells are all formatted (through code) as dd/mm/yy, and
this is verified by checking format/cells. But Excel gets confused
once it finds a date that could be interpreted as dd/mm/yy and
mm/dd/yy. This is obviously not the case for 31/10 but it is the case
for 1/11. This is the problem I've initially observed, and of course
without posting my code up here (which I would prefer not to do!) I
can't demonstrate the full picture. But I'm sure you guys will agree,
no matter what I've done in VBA there is no way we should get a range
of cells looking like that. Also the funny thing is, when you select
one of those november cells, the date appears correctly in the value
window (or whatever it's called) up on top.

I need to get to the bottom of this - I'll strip out code bit by bit,
but suggestions (in particular from Microsoft people that might read
this) are very welcome!
thanks Oisin

 

[JulieD]

Hi Oisin

the other thing you can do, if you're not already, is in your code use the
Format(mydate, "dd/mm/yy") statement before outputting the cells, this is
what i do to ensure that it works.

Cheers
JulieD
PS if you'ld like to email me (julied_ng at hcts dot net dot au) your code
to test i'll be happy to have a look at it.

 

[Ron Rosenfeld]

Thanks Julie and Paul,

I have found in fact that this problem only happens once I have run
part of my application - it doesn't happen if I start Excel and try
the test immediately (thanks for that Julie!). However once I do
populate some cells and perform the test on some other cells I get
this behaviour. What I need to do is to get this problem down to first
principles. What I can say for certain is that Excel behaviour has
changed between Excel 2000 and 2003 (Paul I'm quite happy to accept
that there are not internationalisation settings in Excel, I wouldn't
expect there to be). What I am observing once I copy an array of
variant dates to a range in Excel is the following:

31/10/04 23:40
31/10/04 23:50
1/11/04 00:00 <==================== Get this - changed to US format
1/11/04 00:10
1/11/04 00:20

Now all these cells are all formatted (through code) as dd/mm/yy, and
this is verified by checking format/cells. But Excel gets confused
once it finds a date that could be interpreted as dd/mm/yy and
mm/dd/yy. This is obviously not the case for 31/10 but it is the case
for 1/11. This is the problem I've initially observed, and of course
without posting my code up here (which I would prefer not to do!) I
can't demonstrate the full picture. But I'm sure you guys will agree,
no matter what I've done in VBA there is no way we should get a range
of cells looking like that. Also the funny thing is, when you select
one of those november cells, the date appears correctly in the value
window (or whatever it's called) up on top.

I need to get to the bottom of this - I'll strip out code bit by bit,
but suggestions (in particular from Microsoft people that might read
this) are very welcome!
thanks Oisin

It seems likely to me that the date/time strings that did NOT convert are NOT
really dates, but rather are text. Since they start with an "illegal month" in
US format, they are maintained as text. (The cell formatting is irrelevant in
this instance).

However, when you import a date in the form of 11/1/04, Excel can interpret
months and dates according to it's rules.

So let's see what's "really" there.

After you do an import, format the cells as GENERAL and look at the results.

True dates should get converted to a five digit number plus a fractional part
representing the time. Anything that still looks like a date is really TEXT.

For example:

11 Jan 2004 00:10 --> 37997.00694
01 Nov 2004 00:20 --> 38292.01389


Defining better what's going on should help to solve the problem here.



--ron

 

[Oisin O'Reilly]

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

 

[Ron Rosenfeld]

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