convert american dates from a query
 

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

convert american dates from a query
 

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.

--
Regards,
Tom Ogilvy

"Mika" wrote in message
ups.com...
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

convert american dates from a query
 

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


Tom Ogilvy wrote:
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.

convert american dates from a query
 

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...

convert american dates from a query
 

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.

--
Regards,
Tom Ogilvy


"Roger Govier" wrote in message
...
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


Tom Ogilvy wrote:
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.

convert american dates from a query
 

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/

--
Regards,
Tom Ogilvy


"Mika" wrote in message
ups.com...
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...