WallStreetMojo

WallStreetMojo

Home » Excel, VBA & Power BI » Learn VBA » VBA String to Date

VBA String to Date

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel VBA String to Date

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

One of the common problems we all face with excel is “Date & Time” and are often stored as text values and go unnoticed initially. But when they are required to use that time, we will get to know that those values are stored as text and don’t know how to deal with them at all. “Date & Time” are two combined things in one element, but once those values are stored as text values, it is a pain to work with.

VBA String to Date

How to Convert String Values to Date?

You can download this VBA String to Date Excel Template here – VBA String to Date Excel Template

Example #1

Once the VBA variable is declared and assigned as String, anything assigned to that variable will be treated as 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

In the above code, variable “k” is defined as the “String” data type, and for this variable, we have assigned the value as “10-21”.

Ok, let’s run the code and see what we get in the message box in VBA.

VBA String to Date Example 1

We got the value as 10-21 only, but usually, these values are a date, not string values. So even though the data type assigned is “String,” we can still convert to date by 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 result of the variable “k” in the message box, we have assigned the CDATE function. A small adjustment is made, let’s see how big an impact it makes.

VBA String to Date Example 1-1

Now we would see the result as “Date” no longer as “String” value.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

Example #2

Now, look at the below code for an example.

Code:

Sub String_To_Date()

  Dim k As String
  k = 43599
  MsgBox k

End Sub

At this point in time above code would show the result as “43599,” as we assigned above.

VBA String to Date Example 2

But once we use the CDATE function, it will convert to 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.

Example 2-1

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

We can also apply the format to the date as “DD-MMM-YYYY” to read the date accurately.

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

In the above, I have declared one extra variable to store the result. For this variable, I have applied the CDATE conversion function.

Next, I have used the FORMAT function to apply the format of the “DD-MMM-YYYY” format, and the result will be as shown below.

Example 2-2

With this, we can clearly read the daypart and month part. 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 be a hindrance to format.

Example #3

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

Example 3

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

Below is the code I 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.

Example 3-1

Things to Remember

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

Recommended Articles

This has been a guide to VBA String to Date. Here we discuss how to convert date store in string values to date format in excel VBA using the CDATE function along with examples. You can learn more about VBA functions from the following articles –

0 Shares
Share
Tweet
Share
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>