Excel: Using Regional Settings to Localize Date with Time Formats

Excel date and time formats will automatically localize based on the user’s system setting – if the format is set to a specifier denoted by an asterisk in the cell format dialog.

To format a date with time, we’d typically set a custom format like “mm/dd/yyyy hh:mm:ss AM/PM”. But this format will not fully use the regional settings. If we want to automatically localize a date with time format, we have to do something different. I tried a lot of things but the following “trick” was the only way I found that worked for all regional settings including the AM/PM indicator.

First, add the following VBA to the Workbook_Open event. Column A contains the date values we want to localize. Cell D1’s NumberFormat is set the the default date format and cell E1 to the default time format.

Private Sub Workbook_Open()

    Range("D1").NumberFormat = "m/d/yyyy" 'set to default date format
    Range("E1").NumberFormat = "h:mm:ss AM/PM" 'set to default time format
    Columns("A:A").NumberFormatLocal = Range("D1").NumberFormatLocal + _
            " " + Range("E1").NumberFormatLocal

End Sub

Enter date/time data in column A. Column B contains the same date values as A and is set to the regional date format. I did this just to compare to our localized date/time to see if it is formatted correctly. You may want to create a regional time formatted column as well.

Column C shows the date/time custom formatted with “m/d/yyyy h:mm:ss AM/PM”.

Now, open Control Panel and change your systems regional language setting.

Close your woorkbook and re-open. Here’s the result for Sanskirt (India). Notice how the custom format of column C uses the localized m/d/yyyy separators but does not use the local AM/PM or the correct order for day followed by month:

Spanish (Columbia):

Yi (PRC):

Yoruba (Nigeria):

Russian (Russia):

English (United Kingdom)

Chinese (Traditional, Taiwan):

Tagged with:
Posted in MS Office Tricks