CDATE Function in VBA
VBA CDATE is a data type conversion function which converts a data type which is either text or string to a date data type. Once the value converted to date data type then we can play around with date stuff.
Syntax of CDATE
Below is the syntax of the CDATE function in VBA.
Expression: Expression could be a string or text value or a variable which contains value to be converted to the date data type.
CDATE identifies the date and time format in the computer that we are working on and converts the supplied value to the same date data type. If you supply only day and month and ignores year, then CDATE function takes the systems year, shows along with supplied day and month.
We will see more and more example in the below section.
How to Use the CDATE Function in Excel VBA?
Examples of CDATE Function in excel vba.
Before I show you the example of CDATE look at the below code first.
Sub CDATE_Example1() Dim k As String k = "25-12" MsgBox k End Sub
In the above for the variable “k”, I have assigned the value as “25-12”. When I execute this code we will see the same value in the message box in VBA.
But this can be converted to date by using the VBA CDATE function. For this define one more variable as Date.
Dim k1 As Date
For this variable “k1” assign CDATE function and supply the variable “k” which hold the string “25-12”. And for message box show the variable value of “k1” instead of “k”.
k1 = CDate(k)
Now run the code and see the result in a message box.
So the result is “12/25/2019”.
Closely look at the value that we have supplied, we have supplied “25-12” we haven’t supplied year.
While writing this article current year in my system was 2019, so VBA CDATE converted the string value “25-12” to date and added the system year 2019 to it. So final results read like this 12/25/2019 i.e. 25th December 2019.
Now, look at the below code.
Sub CDATE_Example2() Dim k As Variant Dim kResult As Date k = 43889 kResult = CDate(k) MsgBox kResult End Sub
In the above code for the variable “k”, I have applied the number “43889”. We all know this is a serial number but for another variable “KResult” we have converted this value to date by using “CDATE” function.
The same result of variable “kResult” shown in the message box.
Run the code and see the magic of the function “CDATE”.
It shows the result as “2/28/2020”, if you are not familiar with dates in excel then you must be wondering how did this happen.
For example, enter the same number (43889) in one of the cells in the spreadsheet.
For this apply the format as “DD-MM-YYYY”.
Now click on Ok and see the result.
Now the result has changed from a serial number to date. Because we have applied date format top the serial number it has shown the respective date.
So this means the serial number 43889 is equal to the date 28-02-2020.
So in our VBA code CDATE function has executed the same thing by converting the string value to a date data type.
For this example look at the below code.
Sub CDATE_Example3() Dim Value1 Dim Value2 Dim Value3 Value1 = "December 24, 2019" Value2 = #6/25/2018# Value3 = "18:30:48 PM" MsgBox CDate(Value1) MsgBox CDate(Value2) MsgBox CDate(Value3) End Sub
When we run this code we will get below results.
So, all the values are converted to the date data type with CDATE function.
Things to Remember
- CDATE converts only numbers and string values to the date data type.
- This is useful when we use it with other functions.
- If the wrong data type value is supplied then we will get type mismatch error.
- Since date and time are part of serial number it converts time as well as proper time.
This has been a guide to VBA CDATE. Here we discuss how to use CDATE type conversion function in Excel VBA along with examples and downloadable excel sheet. You can learn more from the following articles –
- Excel VBA CreateObject
- Excel VBA Resize
- LOOKUP in VBA
- DateSerial in VBA
- DATEVALUE in VBA
- DatePart in VBA
- DateAdd in VBA