Visual Basic for Applications:
Parsing a date from a string

How to:

VBA offers a straightforward way to parse a string into a date using the CDate function or the DateValue function. However, it’s crucial that the string is in a recognizable date format.

Here’s a basic example using CDate:

Sub ParseDateUsingCDate()
    Dim dateString As String
    Dim parsedDate As Date
    
    dateString = "2023-04-01"
    parsedDate = CDate(dateString)
    
    Debug.Print "Parsed Date: "; parsedDate
End Sub

If you run this code, the output in the Immediate Window (accessible via Ctrl+G in the VBA editor) would be:

Parsed Date: 4/1/2023 

Alternatively, you can use the DateValue function, which is more specific to dates (ignoring the time part):

Sub ParseDateUsingDateValue()
    Dim dateString As String
    Dim parsedDate As Date
    
    dateString = "April 1, 2023"
    parsedDate = DateValue(dateString)
    
    Debug.Print "Parsed Date using DateValue: "; parsedDate
End Sub

Sample output for this would similarly show in the Immediate Window:

Parsed Date using DateValue: 4/1/2023

Keep in mind that the success of parsing depends on the date format of the string matching system or application settings.

Deep Dive

Internally, when VBA parses a string to a date, it uses the regional settings of the Windows operating system to interpret the date format. This is crucial to understand because a date string that perfectly parses on one system might cause an error on another if they use different date/time settings.

Historically, handling dates has been a common source of bugs in applications, particularly those that are used internationally. This reliance on regional settings in VBA is why some might consider alternatives like the ISO 8601 format (e.g., “YYYY-MM-DD”) for unambiguous date representation and parsing across different systems. Unfortunately, VBA does not natively support ISO 8601, and manual parsing would be needed for strict compliance.

For complex date parsing beyond what CDate or DateValue can handle, or to ensure consistent parsing regardless of system locale settings, programmers may resort to custom parsing functions. These could involve splitting the date string into components (year, month, day) and constructing a date using the DateSerial function. Others might choose more powerful languages or libraries designed with internationalization in mind for such tasks.