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 an 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.
