International date format and user form

  • Thread starter Thread starter James Price at Premier
  • Start date Start date
J

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
 
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)
 
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.
 
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
 
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
 
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.
Click to expand...

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
 
Thanks Rick. You're a star

James

Rick Rothstein said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding Date in an overseas format 20
VBA in Excel 2007 date formatting 2
Vba Date$ 1
Convert Date format UK to US 1
Date formatting 1
International Date formatting 4
Excel is changing date format incorrectly 7
User Forms and Date Formats 4

Back
Top