ExcelBanter - International date format and user form

Thanks Rick. You're a star

James

"Rick Rothstein" wrote:

Thanks for this. A couple of points though. I want to be able to identify
the local international setting so when the user initialises the form and
sees the text box they see the text 'dd/mm/yy' or 'mm/dd/yy' to enter the
date.

Assuming the locale always uses m, d and y for the month, day and year date parts of the date format pattern string, you can use this function to return the text string you want to display in the TextBox...

Function DateFormat() As String
DateFormat = CStr(DateSerial(2003, 1, 2))
DateFormat = Replace(DateFormat, "2003", "yyyy")
DateFormat = Replace(DateFormat, "03", "yy")
DateFormat = Replace(DateFormat, "01", "mm")
DateFormat = Replace(DateFormat, "1", "m")
DateFormat = Replace(DateFormat, "02", "dd")
DateFormat = Replace(DateFormat, "2", "d")
DateFormat = Replace(DateFormat, MonthName(1), "mmmm")
DateFormat = Replace(DateFormat, MonthName(1, True), "mmm")
End Function

To use this function, you would use a statement like this...

TextBox1.Text = DateFormat

If your locale can use different letters for the month, day and year date parts, then this modification to the above should work...

Function DateFormat(TheDate As Date) As String
DateFormat = CStr(DateSerial(2003, 1, 2))
With Application
DateFormat = Replace(DateFormat, "2003", String(4, ..International(xlYearCode)))
DateFormat = Replace(DateFormat, "03", String(2, ..International(xlYearCode)))
DateFormat = Replace(DateFormat, "01", String(2, ..International(xlMonthCode)))
DateFormat = Replace(DateFormat, "1", .International(xlMonthCode))
DateFormat = Replace(DateFormat, "02", String(2, ..International(xlDayCode)))
DateFormat = Replace(DateFormat, "2", .International(xlDayCode))
DateFormat = Replace(DateFormat, MonthName(1), String(4, ..International(xlMonthCode)))
DateFormat = Replace(DateFormat, MonthName(1, True), String(3, ..International(xlMonthCode)))
End With
End Function

--
Rick (MVP - Excel)

"James Price at Premier" wrote in message ...
Thanks for this. A couple of points though. I want to be able to identify
the local international setting so when the user initialises the form and
sees the text box they see the text 'dd/mm/yy' or 'mm/dd/yy' to enter the
date.

The other problem is in Germany for dates they use 'dd.mm.yy' but looking at
your example Excel doesn't recognise say '.' as part of a date.

Cheers

James

"Chip Pearson" wrote:

The following function will format a date to the settings for any
locale.

Function FormatDate(TheDate As Date) As String

Dim DateSep As String
Dim sMM As String
Dim sDD As String
Dim sYY As String
Dim S As String

With Application
If .International(xlDayLeadingZero) Then
sDD = String(2, .International(xlDayCode))
Else
sDD = String(1, .International(xlDayCode))
End If
If .International(xlMonthLeadingZero) Then
sMM = String(2, .International(xlMonthCode))
Else
sMM = String(1, .International(xlMonthCode))
End If
If .International(xl4DigitYears) Then
sYY = String(4, .International(xlYearCode))
Else
sYY = String(2, .International(xlYearCode))
End If
DateSep = .International(xlDateSeparator)

Select Case .International(xlDateOrder)
Case 0 'm/d/y
S = sMM & DateSep & sDD & DateSep & sYY
Case 1 'd/m/y
S = sDD & DateSep & sMM & DateSep & sYY
Case 2 'y/m/d
S = sYY & DateSep & sMM & DateSep & sYY
End Select
End With
FormatDate = Format(TheDate, S)
End Function

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Fri, 3 Apr 2009 07:57:11 -0700, James Price at Premier
wrote:

I want to show the default short data type in a cell but it needs to
automatically know what the internation settings are. For example, if I'm in
the UK the text box inside the form will show 'dd/mm/yy', if US 'mm/dd/yy'
and so on.

Many thanks

James