VBA CDATE Function

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

CDATE Function in VBA

VBA CDATE is a data type conversion function that converts a data type, either text or string, to a date data type. Once we convert the value to date data type, we can play around with date stuff.

Syntax of CDATE

Below is the syntax of the CDATE function in VBA.

CDate Syntax

Expression: Expression could be a string or text value or a variable that contains a value to convert to the date data type.

CDATE identifies the date and time format in the computer we are working on and converts the supplied value to the same date data type. So, for example, if you supply only day and month and ignore year, then the CDATE function takes the system’s year and shows it along with supplied day and month.

We will see more and more examples in the below section.

VBA-CDATE-1

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA CDATE Function (wallstreetmojo.com)

How to Use the CDATE Function in Excel VBA?

Examples of CDATE Function in Excel VBA.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

You can download this VBA CDATE Excel Template here – VBA CDATE Excel Template

Example #1

Before we show you the example of CDATE, look at the below code first.

Code:

Sub CDATE_Example1()

    Dim k As String

    k = "25-12"

    MsgBox k

End Sub
CDate Example 1.1

In the above for the variable “k,” we have assigned the value as “25-12”. Therefore, when we execute this code, we will see the same value in the message box in VBAMessage Box In VBAVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.

CDate Example 1.2

But, we can convert this to date using the VBA CDATE function, which defines one more variable as “Date.”

Code:

Dim k1 As Date
CDate Example 1.3

For this variable, “k1” assigns the CDATE function and supplies the variable “k,” which holds the string “25-12”. For the message box, show the variable value of “k1” instead of “k.”

Code:

k1 = CDate(k)
CDate Example 1.4

Now, run the code and see the result in a message box.

CDate Example 1.5

So the result is “12/25/2019”.

Closely look at the value that we have supplied. For example, we have supplied “25-12,” which we have not supplied this year.

While writing this article, the current year in the system was 2019, so VBA CDATE converted the string value “25-12” to date and added the system year 2019 to it. So, the final results read like this 12/25/2019, i.e., 25th December 2019.

Example #2

Look at the below code.

Code:

Sub CDATE_Example2()

    Dim k As Variant
    Dim kResult As Date

    k = 43889

    kResult = CDate(k)

    MsgBox kResult

End Sub
Example 2.1

In the above code for the variable “k,” we have applied the number “43889”. Of course, we all know this is a serial number, but for another variable, “KResult,” we have converted this value to date using the “CDATE” function.

The same result of the variable “KResult” shows in the message box.

Run the code and see the magic of the function “CDATE.”

CDate Example 2.2

It shows the result as “2/28/2020” if you are unfamiliar with dates in Excel, then you must wonder how this happened.

For example, enter the same number (43889) in one of the cells in the spreadsheet.

CDate Example 2.3

For this, apply the format as “DD-MM-YYYY.”

Example 2.4

Now, click on “OK” and see the result.

Example 2.5

The result has changed from a serial number to date because we have applied the date format to the serial number, showing the respective date.

So, this means the serial number 43889 is equal to the date 28-02-2020.

So, the CDATE function has executed the same thing in our VBA code by converting the string value to a date data type.

Example #3

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
Example 3.1

When we run this code, we will get the below results.

CDATE Output

So, all the values convert to the date data type with the CDATE function.

Things to Remember

Recommended Articles

This article is a guide to VBA CDATE. Here, we discuss how to use the CDATE type conversion function in Excel VBA, along with examples and downloadable Excel sheets. You can learn more from the following articles: –