convert american dates from a query

[Mika]

Hi,

I checked past post about date conversions but couldn't find any that
fix my problem:

- I am doing a query that returns dates (text strings) in the format 1
Dec 2005, and I need to convert them to the user short date format.

Problem is that the xl of those users does not understand "Dec". As
there are "built in" functions that only works in the American format I
was expecting this was going to be an easy one... but so far no
success. Ideas

Mika

 

[Tom Ogilvy]

VBA should understand that date format:

? cdate("1 Dec 2005")
12/01/2005
? datevalue("1 Dec 2005")
12/01/2005

I don't have a non US machine to test it on, but I think converting the
string in VBA to a dateserial should do what you need.

 

[Roger Govier]

Hi Tom

On a UK machine the following is returned

? cdate("1 Dec 2005")
01/12/2005
? datevalue("1 Dec 2005")
01/12/2005

Regards

Roger Govier

 

[Mika]

Thanks for your interest Tom,

They don't work because those functions accept the argument as along as
it is recognised as a valid expresion of a date, and that I guess is
related to the regional settings... In the non american excel they will
give an error: type mismatch.


what I need is a function that "knows" it always receive and american
date...

 

[Tom Ogilvy]

Not really -- that is what is displayed. What is actually returned is the
date serial

? clng(cdate("1 Dec 2005"))
38687

Excel is just extremely helpful in recognizing that this is a date and
displaying it as such in your regional settings

The date serial can be displayed anyway you want and the Months and Days
will be correct.

The date serial is non ambiguous.

 

[Tom Ogilvy]

Your correct - my mistake on this with regards to not recognizing "DEC".

check out Stephen Bullens chapter on international considerations for
suggested solutions:

http://www.oaltd.co.uk/ExcelProgRef/ch22/