Excel/VBA date format query

[BOAC]

VBA module copies a date (dd/mm/yyyy) from a TextBox on sheet A to new Sheet B column B. All well until day >12 when it pastes the date as text only.

Code does

Code:

Cells(z, 2) = Format(TextBox2.Text, "dd/mm/yyyy")

PC is set for UK data 'short' format
Column B is set for date format dd/mm/yyyy

Cannot seem to crack this one!

[Agent86]

Not very elegant but try this

Cells(z, 2)=DATEVALUE(LEFT(TextBox2.Text,2)&"/"&MID(TextBox2.Text,4,2)&"/"&MID(TextBox2.Text,7,4))

so long as the text box input is dd/mm/yyyy it should work.

You can then format the cell to any date format using the standard formatting method.

you can also use ISDATE to validate the textbox input as a valid date.

DATEVALUE works for any text that can be interpreted as a date. The problem is it won't consistently interpret data such as 6/5/2000 as 6 May 2000 ..sometimes it makes it 5 Jun. I found it difficult to make the date settings "stick". Writing code that doesn't rely on the international settings is better and saves the angst for people who don't change the default MS settings ...(or DO ..but don't know what they did!)

[BOAC]

Thank you, Agent - that has fixed it! As you say "Not very elegant" but, hey, who cares in a module? As to why we have to resort to such trickery..............?

It appears to pick up on dd/mm/yy as well.