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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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.
