Bolte IT Consulting

Getting Date format based on locale settings

Handling of Date values can become very annoying. Especially, if Excel always thinks it knows better than the programmer or user *sigh*! Automation is meant to help people, not mess up their work.

In order to help me at least displaying date values in the correct format I implemented a little function to get a pattern string based on a users system locale settings.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
' @Author - Alexander Bolte
' @Change Date - 2013-12-14
' @Description - Determines the date format of the system the application is running on.
' The Excel application is getting the date seperator and the format of date values
' from the systems locale settings.
' @Returns - A String holding literals for Day (dd), Month (mm) and Year (yyyy)
' in the order corresponding the systems locale setting separated using the locales date separator.
Function getDateFormat() As String
    Dim dateFormat As String
    Dim datOrder As Integer
    Dim datSeparator As String
    
On Error GoTo err_handler:
    
    datOrder = Application.International(xlDateOrder)
    datSeparator = Application.International(xlDateSeparator)
    
    If datOrder = 0 Then
        dateFormat = "mm" & datSeparator & "dd" & datSeparator & "yyyy"
    ElseIf datOrder = 1 Then
        dateFormat = "dd" & datSeparator & "mm" & datSeparator & "yyyy"
    ElseIf datOrder = 2 Then
        dateFormat = "yyyy" & datSeparator & "mm" & datSeparator & "dd"
    End If
    
err_handler:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    getDateFormat = dateFormat
End Function
Hits: 7370