2 Examples to use VBA CDate function
The CDate function converts an expression to date type.
CDate function in VBA
The CDate function converts an expression to date type.
Syntax:
CDate(expression)
Where expression can be string, cell text, etc.
An example of converting string to date
For demonstrating the CDate function, we have a string as follows:
Str_dt = “December 12, 2003”
We will convert this to date data type by CDate function and display in the message box:
Sub CDate_ex()
Dim str_dt
str_dt = "December 14, 2003"
MsgBox ("After Converting to Date: " & CDate(str_dt))
End Sub
Output:
An example of using Excel sheet with various date formats using CDate
In this example, we have text in the Excel sheet.
This text is dates in different formats. For example,
- 21-Mar-15
- Dec 25 2016
- January, 1 2020
- 12/24/2016
- 2021, Mar 23
We will create a range of these cells. Then a For..Each loop is used to iterate through each cell of the range.
In each iteration, the CDate function is used to convert cell text to date.
See what we get:
Sub CDate_ex()
Dim rng_dt
Set rng_dt = Range("A2:A6")
Dim x
x = 2
For Each cell In rng_dt
Range("B" & x) = CDate(cell)
x = x + 1
Next cell
End Sub
Result:
You can see, all cell’s text with looking date is converted to date type and displayed in the adjacent B column cell.
