VBA String to Date

What is Excel VBA String to Date?

You can come across many cases in Excel VBA where you will need to translate strings representing a date into actual date values. There is a way to transform an input string to a date value. The VBA String to Date or VBA CDATE feature is a technique that can be used to convert a string in such format.

The manner in which the function works is to first represent the input string as numeric, which is then converted to a date format in excel. Only the date format that the system has configured determines the final date format.

Let’s look at an example wherein we declare two variables, dateString, and convertedDate, to hold the input date string and the converted date, respectively.

The dateString is set to “05/20/22,” representing a date in the “MM/DD/YY” format. We use the VBA String to Date function to convert this string into a date and store it in the variable, convertedDate. Finally, a message box is displayed, showing the converted date.

When executed, this code will display a message box with “Converted Date: 5/20/2022.”

How to Convert String Values to Date?

Step 1: Open Excel and press ALT + F11 to open the VBA editor.

Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

Step 3: In the new module, begin by declaring two variables in your code. One variable will be used to store the string containing the date, and the other will hold the converted date.

For example:

Dim dateString As String

Dim convertedDate As Date

Step 4: Set the “dateString” variable to the string that represents the date you want to convert. Ensure that the string is correctly formatted to represent a valid date.

For example: dateString = “2023-01-15”

Step 5: Utilize the VBA String to Date conversion function to convert the string into a date value. In Excel VBA, the commonly used function for this purpose is CDate. It takes the string as input and performs the conversion.

For example: convertedDate = CDate(dateString)

Step 6: Next, think about adding error handling. Error handling in VBA makes sure that your code handles potential conversion failure scenarios appropriately. To deal with exceptions, you might employ strategies like “On Error Resume Next“.

Step 7: Now that you have successfully converted the string into a date, you can use the convertedDate variable in your VBA code for various date-related operations.

Examples

Example #1

In this example, we will see how to use the VBA String to Date function to convert the string “2023-01-15” into a date format and then display the converted date using a message box.

Step 1: In the new module, we first define a VBA subroutine named ConvertBasicDate.

We then declare two variables, “dateString” to store the input date string and “convertedDate” to store the converted date as a Date data type.

Step 2: Here, we assign the date in the form of a string “2023-01-15” to the dateString variable. This string represents a date in the format “YYYY-MM-DD.”

Step 3: In this line, we use the CDate VBA function to convert the dateString to a date. The CDate function takes the string dateString and converts it to a Date VBA data type.

The result is stored in the convertedDate variable.

Step 4: Finally, we display a message box with the converted date value using the MsgBox function. The message includes the text “Converted Date: ” followed by the value of convertedDate.

Step 5: When you run this code, a message box in VBA will pop up displaying “Converted Date: 1/15/2023,” indicating that the string “2023-01-15” has been successfully converted to a date.

Here is the complete  code:

Sub ConvertBasicDate()

    Dim dateString As String

    Dim convertedDate As Date

    dateString = “2023-01-15”

    convertedDate = CDate(dateString)

    MsgBox “Converted Date: ” & convertedDate

End Sub

Example #2

In this example, we will see the VBA String to Date function’s ability to convert the string “01/15/23,” which is in a custom date format (“MM/DD/YY”), into a date value.

Step 1: Similar to the first example, we declare two variables, dateString and convertedDate.

Step 2: Here, we assign the string “01/15/23” to the dateString variable, which represents the date in “MM/DD/YY” format.

Step 3: This line uses the CDate function to convert the dateString into a date and stores it in the convertedDate variable.

Step 4: We display a message box with the converted date. In this case, the message box will display “Converted Date: 1/15/2023,” converted into the default date format.

Step 5: Now click on Run. When you run this code, it converts the string “01/15/23” in the custom format (“MM/DD/YY”) into a date value and then displays the converted date in a message box.

Here is the full code:

Sub ConvertCustomFormat()

    Dim dateString As String

    Dim convertedDate As Date

    dateString = “01/15/23”

    convertedDate = CDate(dateString)

    MsgBox “Converted Date: ” & convertedDate

End Sub

Example #3

In this example, we will learn how the VBA String to Date function can efficiently convert a string, “2023-01-15 14:30:00,” containing both date and time information into a date-time value.

Step 1: In the new module, once again, we declare two variables, dateTimeString and convertedDateTime.

Step 2: In this step, we assign the string “2023-01-15 14:30:00” to dateTimeString, which contains both date and time information.

Step 3: Now, we use the VBA String to Date function to convert the dateTimeString into a date-time value and store it in the variable, convertedDateTime.

Step 4: We display a message box showing the converted date-time. When executed, the message box will display “Converted Date-Time: 1/15/2023 2:30:00 PM,” including both date and time information.

Step 5: When you execute the code, the output reflects the date “January 15, 2023,” along with the time “2:30:00 PM,” as converted from the input string containing both date and time information.

Here is the full code:

Sub ConvertDateTime()

    Dim dateTimeString As String

    Dim convertedDateTime As Date

    dateTimeString = “2023-01-15 14:30:00”

    convertedDateTime = CDate(dateTimeString)

    MsgBox “Converted Date-Time: ” & convertedDateTime

End Sub

Important Things To Note

1. Excel’s date system has limitations, and it may only accurately handle dates within the range of January 1, 1900, to December 31, 9999. If you need to work with dates outside this range, consider alternative solutions or date libraries.
2. Be aware of the date delimiters used in the input string. Different regions may use different symbols or characters to separate day, month, and year components. Ensure that your code handles these delimiters correctly.
3. The VBA string-to-date format refers to the ability to control the appearance and layout of a date when converting it from a string.
4. When working with date-time values, be mindful of time zone differences if your application involves global time data. Consider using functions like DateAdd to handle time zone conversions or adjustments when needed.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA String to Date. Here we learn How to Convert String Values to Date along with examples. You can learn more from the following articles –

• VBA String Array
• VBA String to Integer
• VBA Split String into Array