dd/mm/yyyy becoming mm/dd/yyyy

[snuggles79]

On my userform I have a "date" textbox and have put the following coding into visual basic:

Private Sub contdate_Exit
If Not IsDate(contdate) Then MsgBox "Input must be a date in the format: 'dd/mm/yyyy'" Cancel = True
Else contdate = Format(contdate, "dd/mm/yyyy")
End If
End Sub



Now, when the date field is filled in and the data transferred to the spreadsheet an oddity happens.

If the "date" part is above 12 then there are no problems and it enters it as dd/mm/yyyy but, if the "date" part is 12 or lower it gets put in as mm/dd/yyyy for some bizarre reason.

Any ideas how I can correct this?

I have formatted the cells as dd/mm/yyyy and it makes no difference.

Thanks in advance guys

 

[snuggles79]

Oh One More Thing ....

Below is the part of the code I am using to transfer the form data into a spreadsheet (for the date):

Worksheets("sheet1").Range("B2") = witdiary.contdatevalue

 

[SydneyGeek]

I hate this. Does it to me all the time.

When you convert a date to a string (eg, with the Format function), VBA in Excel and Access then assumes that you must be working with American dates (the Union Jack tells me you aren't)...

I solve it in one of 2 ways. I use DATESERIAL to push the year, month and days where they belong (the Excel worksheet function is just DATE, but it works the same way); or else I convert the dates using CDbl, export the dates as raw numbers, and format them as dates once they land in Excel.

Denis

 

[snuggles79]

Ermmm I'm quite new to Visual Basic so could you explain what could I would use please

 

[snuggles79]

(

Anybody out there who can help before I throw my PC out the window?

 

[erik.van.geit]

Just throw it away, take a plan and go live in America where they reverse the "natural" order of dates

Hello,

take a look at this thread
http://www.mrexcel.com/board2/viewtopic.php?t=155199
I know it's rather "heavy" ...

two main results
Tom Urtis' datepicker (see end of thread)
using DateValue
I cannot test your code but it should be something like this
(according to the codeline you posted)

Worksheets("sheet1").Range("B2") = DateValue(witdiary.contdatevalue)

kind regards,
Erik

 

[snuggles79]

Thank you so much - that works perfectly!

Though I did have to add an extra .

Worksheets("sheet1").Range("B2") = DateValue(witdiary.contdate.value)

 

[erik.van.geit]

you're welcome !!

and ... by the way
<font face="Courier New" size="+1" color="blue" style="background: url(http://www.balloondoggies.com/vonpookie/misc/sparkle.gif)">WELCOME TO THE BOARD !!!!</font>