VBA String to Date

In VBA, there is a method through which we can convert a given string to a date. The method is known as the CDATE function in VBA. It is an inbuilt function in VBA, and the parts required for this function are first to convert the string to a number, then convert the given number to date. The result format depends on the system date format only.

One of the common problems we all face with Excel is "Date & Time," which are often stored as text values and go unnoticed initially. But, when they require the use of that time, we know that those values are stored as text and don't know how to deal with them. That is because "Date & Time" are two combined things in one element. But, once those values as text values are stored, it is a pain to work with.

Example #1

Once the VBA variable is declared and assigned as String, anything assigned to that variable will be treated as a string only. For example, look at the below code.

Code:

Sub String_To_Date()

  Dim k As String
  k = "10-21"
  MsgBox k

End Sub

The above code defines the variable "k" as the "String" data type. We have assigned this variable the value of "10-21."

Run the code and see what we get in the message box in VBA.

We only got the value as 10-21. But usually, these values are dates, not string values. So, even though the data type assigned is "String," we can still convert to date using the data type conversion function CDATE VBA.

Code:

Sub String_To_Date()

  Dim k As String
  k = "10-21"
  MsgBox CDate(k)

End Sub

In the above, before we show the variable "k" result in the message box. Then, we assigned the CDATE function. Then, we made a small adjustment. Finally, let us see how big an impact it makes.

Now, we would see the result as "Date" no longer as a "String" value.

Example #2

Look at the below code.

Code:

Sub String_To_Date()

  Dim k As String
  k = 43599
  MsgBox k

End Sub

The above code would show the result as "43599," as we assigned above.

But once we use the CDATE function, it will convert to the date value.

Code:

Sub String_To_Date()

  Dim k As String
  k = 43599
  MsgBox CDate(k)

End Sub

The result after applying the CDATE function is as follows.

Since Excel stored the date as serial numbers, our assigned serial number 43599 equals the date 05/14/2019 when the date format is applied.

To read the date accurately, we can apply the format to the date as "DD-MMM-YYYY."

Code:

Sub String_To_Date1()

  Dim k As String
  Dim DateValue As Date

  k = 43599
  DateValue = CDate(k)
  MsgBox Format(DateValue, "DD-MMM-YYYY")

End Sub

We have declared one extra variable to store the result in the above. For this variable, we have applied the CDATE conversion function.

Next, we used the FORMAT function to apply the "DD-MMM-YYYY" format. The result will be as shown below.

With this, we can read the daypart and month part. Of course, it also depends on your system date format in excel. Since my system date format was "MM-DD-YYYY," it was showing like that, but that should not hinder the format.

Example #3

Now, we will see how dates format as text values in worksheet cells. Below is the image of the dates stored as text in a worksheet.

In column A from A2 to A12, we have date-looking values, but when we look at the format tab, it shows "Text" format. So, now we need to convert these values from text to date.

Below is the code we have written to convert the text-formatted date values to actual dates.

Code:

Sub String_To_Date2()

Dim k As Long

 'Data is in more than one cell, so need to loop through each cell
 'Open For Loop

For k = 2 To 12
 'Data starts from 2nd row and ends at 12th row, so 2 to 12
   Cells(k, 2).Value = CDate(Cells(k, 1).Value)
Next k

End Sub

If you run the code, it will give us the below result.

• The CDATE is a data type conversion function that can convert VBA string stored date to actual date values.
• The result of the CDATE function format depends on the system date format only.
• Dates are stored as serial numbers in Excel, so formatting is required to show them as dates.