Re: International date format and user form

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

--
Rick (MVP - Excel)


"Rick Rothstein" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message news:O68KvYItJHA.528@xxxxxxxxxxxxxxxxxxxxxxx
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 (MVP - Excel)


"Chip Pearson" <chip@xxxxxxxxxxxx> wrote in message
news:pnect4l53pkc92tu6ogih6f2uq1qrd9jo3@xxxxxxxxxx
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
<JamesPriceatPremier@xxxxxxxxxxxxxxxxxxxxxxxxx> 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

.

Loading