Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Convert European Date format to American Format | Excel Discussion (Misc queries) | |||
convert american dates from a query | Excel Programming | |||
How do I convert spreadsheet from Canadian $ to American $? | Excel Discussion (Misc queries) | |||
English and American dates... | Excel Programming |