Retrieving short system date format in VBA

  • Thread starter Thread starter RoccoCoetzee
  • Start date Start date
R

RoccoCoetzee

Hi

Can someone please help me retrieving the system short date format (I
presume it’s from local)

I have managed to do that from Visual Studio (VB) with the following line.

SystemShortDateFormat =
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern

How do I accomplish this in VBA?
 
You are looking for the pattern of the short date format, not a date in that
format, correct? Here is one way to get it...

Function DateFormat() As String
DateFormat = FormatDateTime(DateSerial(2003, 1, 2), vbShortDate)
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

If it helps any, you can get the time format pattern using this function...

Function TimeFormat() As String
TimeFormat = CStr(TimeSerial(13, 22, 44))
TimeFormat = Replace(TimeFormat, "22", "mm")
TimeFormat = Replace(TimeFormat, "44", "ss")
If InStr(TimeFormat, "13") > 0 Then
TimeFormat = Replace(TimeFormat, "13", "HH")
If InStr(CStr(TimeSerial(1, 22, 44)), "0") = 0 Then
TimeFormat = Replace(TimeFormat, "HH", "H")
End If
Else
TimeFormat = Replace(TimeFormat, "1", "h")
TimeFormat = Replace(TimeFormat, "0", "h")
TimeFormat = Replace(TimeFormat, "PM", "tt", , , vbTextCompare)
End If
End Function
 
Hi Jacob

Thank you for replying so quickly.

This function will return a date value formatted in the short date format
E.g. (30/09/2009)

What I’m looking for is something that would return the format as string
E.g. ("dd/mm/yyyy") or ("mm/dd/yyyy").

The reason why is because I have a form that gets text input from users and
then should compile the date from what was selected amongst a number of other
input.

The problem is that not all my users have the same locale settings thus the
date is being compiled incorrectly because of the date format.

I have fixed this before with one of my VS project and want to use the same
code only problem is that the line,
“SystemShortDateFormat =
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern†does not work in VBA.

The value of SystemShortDateFormat would then be = "DD-MM-YYYY" or some
thing to that effect.

So I’m looking for a function of some sort returning the pattern / format
E.g. ("dd/mm/yyyy)" or ("mm/dd/yyyy") as with the VS class.
 
You can use the Format() function

Msgbox Format(Date,"mm/dd/yyyy")
OR
Msgbox Format(Date,"dd/mmm/yyyy")

If this post helps click Yes
 
Did you see my posting?

--
Rick (MVP - Excel)


RoccoCoetzee said:
Hi Jacob

Thank you for replying so quickly.

This function will return a date value formatted in the short date format
E.g. (30/09/2009)

What I’m looking for is something that would return the format as string
E.g. ("dd/mm/yyyy") or ("mm/dd/yyyy").

The reason why is because I have a form that gets text input from users
and
then should compile the date from what was selected amongst a number of
other
input.

The problem is that not all my users have the same locale settings thus
the
date is being compiled incorrectly because of the date format.

I have fixed this before with one of my VS project and want to use the
same
code only problem is that the line,
“SystemShortDateFormat =
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePatternâ€
does not work in VBA.

The value of SystemShortDateFormat would then be = "DD-MM-YYYY" or some
thing to that effect.

So I’m looking for a function of some sort returning the pattern / format
E.g. ("dd/mm/yyyy)" or ("mm/dd/yyyy") as with the VS class.
Click to expand...
 
Back
Top