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

 

[joel]

Cells that are formated as General should take on the internatio
settings. I believe in 2007 there may be some bugs and are revvertin
to US format. Make sure you have all the office updates.

Using the floowing code will not solve this problem

datevar = format([Date from text box], )

If the cell is general formating it will perform the same algorithm a
if you manually typed the date and do a conversion.

Yo could also have the cells format as "dd/mm/yyyy" which could caus
the problem.

The solution would be to format the cells as "dd/mm/yyyy" or use in
formula then following

datevar.numberformat = "dd/mm/yyyy

 

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

 

[joel]

Dates are stored in excel as a number with 1 = Jan 1, 1900. Every day
increments by 1 so Jan 2, 1900 is 2. An hour is stored as 1/24 and a
minute is stored a 1/(24*60) with midnight equa 0. So noon is equal to
1/2 (.5) and 6:00AM equals 1/4 (.25).

Formating a date doesn't change the whay it is stored into the
spreadsheet. A date is stored as the number equivant. Tformating only
change the way the date iis displayed.

My guess is this is not an excel problem. since you are copying form
text boxes the date goes into the clipboard. So there is a problem
either going from the text box to the clip board or from the clip board
into excel.

You could try opening up Notepad and see what happens if you copy a
date from excel into Notepad and from Notepad to excel. This may help
isolate the problem. You can also view the clip board data by opening
up the TaskPane from the worksheet view menu and then pressing the down
arrow in the View Pane and select ClipBoard.

 

[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