WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA CDATE Function

VBA CDATE Function

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

By Jeevan A Y

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.

CDate Syntax

Expression: Expression could be a string or text value or a variable that contains a 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 the CDATE function takes the systems year, shows along with supplied day and month.

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

VBA-CDATE

How to Use the CDATE Function in Excel VBA?

Examples of CDATE Function in Excel VBA.

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

Example #1

Before I 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,” 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.

CDate Example 1.2

But this can be converted to date by using the VBA CDATE function, for this define one more variable as Date.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

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”. And 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. 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.

Example #2

Now, 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,” 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 the “CDATE” function.

The same result of the variable “KResult” is shown 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 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.

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

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.

Example #3

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

Example 3.1

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

CDATE Output

So, all the values are converted to the date data type with the 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 a type mismatch error.
  • Since date and time are part of the serial number, it converts time as well as proper time.

Recommended Articles

This has been 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 –

  • LOOKUP in VBA
  • DateSerial in VBA
  • DATEVALUE in VBA
  • DateAdd in VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Category iconExcel, VBA & Power BI,  Learn VBA

Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA CDATE Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More