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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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:
 

Forum statistics

Threads
1,144,280
Messages
5,723,470
Members
422,499
Latest member
think say

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top