Excel/VBA date format query

[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!)