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:
English (United Kingdom)
Chinese (Traditional, Taiwan):