International date format and user form

[James Price at Premier]

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

 

[Chip Pearson]

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)

 

[Rick Rothstein]

Here is a past function I originally developed for a question over in the
compiled VB newsgroups which will format a date in the same format that
Windows is using...

Function DateFormat(TheDate As Date) 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")
DateFormat = Format(TheDate, DateFormat)
End Function

Just to note, it also handles date formats in which the month name is
abbreviated or spelled out in full.

 

[Rick Rothstein]

I don't have experience with International issues, so I'm not sure if the Format statement will use the correct day, month, year symbols when M, d and Y are used in the pattern string. If it does not, then I would think this modification to my function would work correctly...

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
DateFormat = Format(TheDate, DateFormat)
End Function

 

[James Price at Premier]

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

 

[Rick Rothstein]

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

 

[James Price at Premier]

Thanks Rick. You're a star

James

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