dd/mm/yyyy becoming mm/dd/yyyy

snuggles79

New Member
Joined
Sep 25, 2006
Messages
8
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

snuggles79

New Member
Joined
Sep 25, 2006
Messages
8
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Sep 25, 2006
Messages
8
Ermmm I'm quite new to Visual Basic so could you explain what could I would use please :)
 

snuggles79

New Member
Joined
Sep 25, 2006
Messages
8

ADVERTISEMENT

:eek:(

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

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Just throw it away, take a plan and go live in America where they reverse the "natural" order of dates :LOL:

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)
Code:
Worksheets("sheet1").Range("B2") = DateValue(witdiary.contdatevalue)

kind regards,
Erik
 

snuggles79

New Member
Joined
Sep 25, 2006
Messages
8
:)

Thank you so much - that works perfectly!

Though I did have to add an extra . :)

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

:biggrin: :biggrin: :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,113,920
Messages
5,545,029
Members
410,647
Latest member
bernardazar
Top