UK to US date format

[David.Wilson]

I have a macro that reads;
- data from a form, and writes it to a central data sheet
- and another macro that can retrieve the data and present it on the
form again.

In both macros the date is set as "dd-mmm-yy" format. However I am
having a problem when there is a date such as
05-Jul-04. The 05 and the 07 of the day and month keep getting
transposed so the date turns into 07-May-04 (!!!).

Other dates (I guess ones where the day value is bigger than 12), seem
fine. The network and applications at my work are set to UK date
format, so why are these values changing over to US format?

 

[Jan Karel Pieterse]

Hi David.Wilson,

Other dates (I guess ones where the day value is bigger than 12), seem
fine. The network and applications at my work are set to UK date
format, so why are these values changing over to US format?

Because VBA speaks American.

To avoid the trouble you have, dim a variable as a Date and deliberately
convert the string of the form's control to a date and put that into the
Date variable:

Sub test()
Dim dDate As Date
Dim sDate As String
sDate = InputBox("Please enter a date")' a string
dDate = CDate(sDate) 'Now convert string to Date, using regional
settings
ActiveCell.Value = dDate
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Tom Ogilvy]

VBA is US Centric. If you work with the date serial number (the way dates
are stored) rather than the string representation of the date, you shouldn't
have a problem. If you mean Userform when you say "form", then convert the
string date in the text box to a date serial number using cDate. This
should pay attention to your regional settings.

Dim dtDate as Date
dtDate = cDate(textbox1.text)

as an example.