Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving short system date format in VBA
Hi
Can someone please help me retrieving the system short date format (I presume its from local) I have managed to do that from Visual Studio (VB) with the following line. SystemShortDateFormat = System.Globalization.CultureInfo.CurrentCulture.Da teTimeFormat.ShortDatePattern How do I accomplish this in VBA? -- Rocco Coetzee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving short system date format in VBA
Try
Formatdatetime(date,vbShortDate) If this post helps click Yes --------------- Jacob Skaria "RoccoCoetzee" wrote: Hi Can someone please help me retrieving the system short date format (I presume its from local) I have managed to do that from Visual Studio (VB) with the following line. SystemShortDateFormat = System.Globalization.CultureInfo.CurrentCulture.Da teTimeFormat.ShortDatePattern How do I accomplish this in VBA? -- Rocco Coetzee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving short system date format 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 -- Rick (MVP - Excel) "RoccoCoetzee" wrote in message ... Hi Can someone please help me retrieving the system short date format (I presume its from local) I have managed to do that from Visual Studio (VB) with the following line. SystemShortDateFormat = System.Globalization.CultureInfo.CurrentCulture.Da teTimeFormat.ShortDatePattern How do I accomplish this in VBA? -- Rocco Coetzee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving short system date format in VBA
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 Im 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.Da teTimeFormat.ShortDatePattern€ť does not work in VBA. The value of SystemShortDateFormat would then be = "DD-MM-YYYY" or some thing to that effect. So Im 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. -- Rocco Coetzee "Jacob Skaria" wrote: Try Formatdatetime(date,vbShortDate) If this post helps click Yes --------------- Jacob Skaria "RoccoCoetzee" wrote: Hi Can someone please help me retrieving the system short date format (I presume its from local) I have managed to do that from Visual Studio (VB) with the following line. SystemShortDateFormat = System.Globalization.CultureInfo.CurrentCulture.Da teTimeFormat.ShortDatePattern How do I accomplish this in VBA? -- Rocco Coetzee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving short system date format in VBA
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 --------------- Jacob Skaria "RoccoCoetzee" wrote: 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 Im 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.Da teTimeFormat.ShortDatePattern€ť does not work in VBA. The value of SystemShortDateFormat would then be = "DD-MM-YYYY" or some thing to that effect. So Im 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. -- Rocco Coetzee "Jacob Skaria" wrote: Try Formatdatetime(date,vbShortDate) If this post helps click Yes --------------- Jacob Skaria "RoccoCoetzee" wrote: Hi Can someone please help me retrieving the system short date format (I presume its from local) I have managed to do that from Visual Studio (VB) with the following line. SystemShortDateFormat = System.Globalization.CultureInfo.CurrentCulture.Da teTimeFormat.ShortDatePattern How do I accomplish this in VBA? -- Rocco Coetzee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving short system date format in VBA
Did you see my posting?
-- Rick (MVP - Excel) "RoccoCoetzee" wrote in message ... 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 Im 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.Da teTimeFormat.ShortDatePattern€ť does not work in VBA. The value of SystemShortDateFormat would then be = "DD-MM-YYYY" or some thing to that effect. So Im 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. -- Rocco Coetzee "Jacob Skaria" wrote: Try Formatdatetime(date,vbShortDate) If this post helps click Yes --------------- Jacob Skaria "RoccoCoetzee" wrote: Hi Can someone please help me retrieving the system short date format (I presume its from local) I have managed to do that from Visual Studio (VB) with the following line. SystemShortDateFormat = System.Globalization.CultureInfo.CurrentCulture.Da teTimeFormat.ShortDatePattern How do I accomplish this in VBA? -- Rocco Coetzee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why can't I convert Long date format to Short date | Excel Discussion (Misc queries) | |||
Storing data in Short Date format | New Users to Excel | |||
short date format not working while i try to excel afte | Excel Discussion (Misc queries) | |||
Format an Excel Column in the window's short date format. | Excel Programming | |||
Where is a sample formula for retrieving the system date? | Excel Discussion (Misc queries) |