Date Time Format: Internationale

Many times in globalized world of today, our creation of spread sheets attracts viewers from different parts of the world with “not so good” friendly regional settings on their computers. Due to this our work doesn’t show up as required, and many times the entire process fails.
In order to overcome this issue, I recently came across brilliant solutions to make our workbook globally adaptable to take on any cultural settings we put them under.

And the magic lies in the following code below, which returns the PC region specific identifier used for the item of interest it represents:

Application.ExecuteExcel4Macro("Index(Get.Workspace(37),(##Code##))")

'Example
'Application.ExecuteExcel4Macro("Index(Get.Workspace(37),(21))")
'Returns "d" represnting Day of date in GB, and "j" represnting Day of date in FR
'Array
'index Results Information
'1      1   Number corresponding to the country version of Microsoft Excel.
'2      44  Number corresponding to the current country setting in the 
'           Microsoft Windows Control Panel or the country number as determined
'           by your Apple system software
'3      .   Decimal Separator
'4      ,   Zero (or 1000) separator
'5      ,   List separator
'6      R   Row character
'7      C   Column character
'8      r   Lowercase row character
'9      c   Lowercase column character
'10     [   Character used instead of the left bracket ([)
'11     ]   Character used instead of the right bracket (])
'12     {   Character used instead of the left bracket ({)
'13     }   Character used instead of the right bracket (})
'14     ,   Column separator
'15     ;   Row separator
'16     @   Alternate array item separator to use if the current array separator is the same as the decimal separator
'17     /   Date separator
'18     :   Time Separator
'19     y   Year symbol
'20     m   Month symbol
'21     d   Day symbol
'22     h   Hour symbol
'23     m   Minute symbol
'24     s   Second symbol
'25     £   Currency symbol
'26     General General symbol
'27     2   Number of decimal digits to use in currency formats
'28     1   Number indicating the current format for negative currencies
'29     2   Number of decimal digits to use in noncurrency number formats
'30     3   Number of characters to use in month names
'31     3   Number of characters to use in weekday names
'32     1   Number indicating the date order
'33     FALSE   TRUE if using 24-hour time; FALSE if using 12-hour time.
'34     FALSE   TRUE if not displaying functions in English; otherwise, returns FALSE.
'35     TRUE    TRUE if using the metric system; FALSE if using the English measurement system.
'36     FALSE   TRUE if a space is added before the currency symbol; otherwise, returns FALSE.
'37     TRUE    TRUE if currency symbol precedes currency values; FALSE if it follows currency values.
'38     TRUE    TRUE if using minus sign for negative numbers; FALSE if using parentheses.
'39     TRUE    TRUE if trailing zeros are displayed for zero currency values; otherwise, returns
'40     TRUE    TRUE if leading zeros are displayed for zero currency values; otherwise, returns
'41     TRUE    TRUE if leading zero is displayed in months (when  months are displayed as numbers); otherwise, returns FALSE.
'42     TRUE    TRUE if leading zero is shown in days (when days are displayed as numbers); otherwise, returns FALSE.
'43     TRUE    TRUE if using four-digit years; FALSE if using two-digit years.
'44     FALSE   TRUE if date order is month-day-year when displaying dates in long form; FALSE if date order is day-month-year.
'45     TRUE    TRUE if leading zero is shown in the time; otherwise, returns FALSE.
 

Using this technique, with the combination of unique named ranges and “REPT” workbook function we can create the formats we would require across the workbook and make the format functions like “TEXT” work seamlessly across different regional settings based PC’s.
Below are some of the examples illustrating the use of the technique to identify your PC settings information:

Download solution: Workspace Information

Download solution: International Example

Refrences:
http://www.rondebruin.nl/international.htm