Problems with date formats

[Vagabond]

My personal gremlin seems to be that,for no apparent reason (although I am
sure there must be one) when moving dates around in VBA they change from UK
format to US format. A date I pick up from, say, a text box as 12/01/2010
(being 12th Jan) will arrive in the prescribed cell as 01/12/2010.

I seem to have tried everything including assigning the date to a variable
using the datevar = format([Date from text box], "dd/mm/yyyy") in an attempt
to force it to behave. Nothing seems to work consistently.

I am using Excel 07 and my machine locale setting is UK.

I am sure that I must be missing a trick but I'm not sure what. Any ideas,
anyone?

 

[Bob Phillips]

That is a VBA 'feature', it only knows US dates.

Cast it

CDate(TextBox1.Text)

HTH

Bob

 

[Libby]

I use Dateserial to get the elements of the date and combine them in the
appropriate format. I've also found that cell formatting will override the
date being entered, so if the cell is formatted in US format then that will
override how the date is presented from the textbox.

 

[michdenis]

Hi,

An example : http://cjoint.com/?botWd33EPR


"Vagabond" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
My personal gremlin seems to be that,for no apparent reason (although I am
sure there must be one) when moving dates around in VBA they change from UK
format to US format. A date I pick up from, say, a text box as 12/01/2010
(being 12th Jan) will arrive in the prescribed cell as 01/12/2010.

I seem to have tried everything including assigning the date to a variable
using the datevar = format([Date from text box], "dd/mm/yyyy") in an attempt
to force it to behave. Nothing seems to work consistently.

I am using Excel 07 and my machine locale setting is UK.

I am sure that I must be missing a trick but I'm not sure what. Any ideas,
anyone?

 

[Bob Phillips]

What clipboard?

I guess you are US based so you don't see the problem, but I can assure you
that the problem is in the way VBA treats dates. Unless a date is
unambiguous, such as 13/8/2009, VBA will treat it as a US date, regardless
of your date settings. So, if I, who is UK based, enter 12/10/2009 in a
textbox, and then drop that textbox value into a cell, it ends up as 10th
Dec 2009, ignoring my format for clarity.

As I said, the solution is to cast the textbox value. such as

Range("A1").Value = CDate(TextBox1.Text)

Bob