EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home VBA VBA Resources VBA Conversion Functions VBA CDate
 

VBA CDate

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VBA CDate

Excel VBA CDate Function

Have you heard of the function or command by which we can convert anything into date and time? Yes, along with Date function we have CDate function in VBA which does so. CDate is the function of excel but this can also be done in VBA as well. CDate converts anything but into standard Date format. This can be used for converting time as well along with Date.

 

 

Syntax of CDate is the easiest syntax we have ever seen. CDate only considers expression such as Date and time in any format as input. Below is the syntax of it.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of CDate

We just need to feed any number, date or time in any format that we have and CDate will automatically convert that into standard Date and Time format.

How to Use Excel VBA CDate Function?

We will learn how to use a VBA CDate function with a few examples in Excel.

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

VBA CDate – Example #1

We will take a simple example first. In this example, we will try to convert one simple type of date into a standard format which is actually inbuilt in excel by default.

Follow the below steps to use CDate function in VBA.

Step 1: Open a Module which is available in the Insert menu tab as shown below.

VBA CDate Example 1-1

Step 2: Now write the subprocedure of VBA CDate in any name as shown below. But it is recommended to write the name of subprocedure in the name of performed work mainly.

Code:

Sub VBA_CDate()

End Sub

VBA CDate Example 1-2

Step 3: Now declare a variable let’s say it is Input1 as String. Considering the data type as String because we will be quoting the input in the combination of numbers and alphabets.

Code:

Sub VBA_CDate()

Dim Input1 As String

End Sub

VBA CDate Example 1-3

Step 4: Now we will declare another variable by which we will see the output. And this variable will be used to see the dates.

Code:

Sub VBA_CDate()

Dim Input1 As String
Dim FormatDate As Date

End Sub

VBA CDate Example 1-4

Step 5: Now choose any date which is in the combination of numbers and alphabets and quote that in inverted commas as shown below.

Code:

Sub VBA_CDate()

Dim Input1 As String
Dim FormatDate As Date
Input1 = "Sept 1, 2019"

End Sub

VBA CDate Example 1-5

Step 6: To convert the input date into a standard format we will use CDate function as shown below with the FormatDate variable which was declared above. And use the value stored in the Input1 variable.

Code:

Sub VBA_CDate()

Dim Input1 As String
Dim FormatDate As Date
Input1 = "Sept 1, 2019"
FormatDate = CDate(Input1)

End Sub

VBA CDate Example 1-6

Step 7: And to see the output we will use Msgbox to assign it with FormatDate function of Date.

Code:

Sub VBA_CDate()

Dim Input1 As String
Dim FormatDate As Date
Input1 = "Sept 1, 2019"
FormatDate = CDate(Input1)
MsgBox FormatDate

End Sub

Use Msgbox

Step 8: Now run the code by pressing the F5 key or by clicking on Play Button. We will get the date which we have chosen as 1 Sept 2019, is now got converted into standard date format as 9/1/2019 as shown below.

VBA CDate Example 1-8

We can try different multiple combinations of dates that really exist and see what kind of standard output we get.

VBA CDate – Example #2

In this example, we will see different types of date and time which exist and what kind of output we would get while using VBA CDate. For this follow the below steps:

Step 1: Write the subprocedure of VBA CDate as shown below.

Code:

Sub VBA_CDate2()

End Sub

VBA CDate Example 2-1

Step 2: Now we will declare 3-4 different variables of Data type Date. Let’s declare the first variable as Date1 and give it the data type as Date as shown below.

Code:

Sub VBA_CDate2()

Dim Date1 As Date

End Sub

VBA CDate Example 2-2

Step 3: Now assign any number which we want to convert it in Date format. We have chosen a random number as 12345.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = "12345"

End Sub

VBA CDate Example 2-3

Step 4: In a similar way define another variable Date2 as date type Date as shown below.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = "12345"

Dim Date2 As Date

End Sub

Declare Date2 Variable

Step 5: Now again in the variable Date2, consider putting a date in any format. Here we have kept 12/3/45 as our date input.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = "12345"

Dim Date2 As Date
Date2 = "12/3/45"

End Sub

VBA CDate Example 2-5

Step 6: Further, we will again declare another variable Date3 as Date.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = "12345"

Dim Date2 As Date
Date2 = "12/3/45"

Dim Date3 As Date

End Sub

Declare Date3 Variable

Step 7: Here we will assign the value of any time as shown below as 12:10 PM in 24 hours format.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = "12345"

Dim Date2 As Date
Date2 = "12/3/45"

Dim Date3 As Date
Date3 = "00:10:00"

End Sub

VBA CDate Example 2-7

Step 8: Now lastly we will declare another Date4 variable as Date.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = "12345"

Dim Date2 As Date
Date2 = "12/3/45"

Dim Date3 As Date
Date3 = "00:10:00"

Dim Date4 As Date

End Sub

Declare Date4 Variable

Step 9: And here we will give some decimal value like 0.123 or you can choose any value as required.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = "12345"

Dim Date2 As Date
Date2 = "12/3/45"

Dim Date3 As Date
Date3 = "00:10:00"

Dim Date4 As Date
Date4 = "0.123"

End Sub

VBA CDate Example 2-9

Now there are 2 ways to see the output of the values stored in various variables declared above. MsgBox will only allow us to see all values simultaneously but by using Debug.print will allow us to see all the variables output in one go.

Step 10: So, here it is better if we choose Debug.Print as shown below. And in the same line assign all the variable starting from Date 1 to Date 4.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = "12345"

Dim Date2 As Date
Date2 = "12/3/45"

Dim Date3 As Date
Date3 = "00:10:00"

Dim Date4 As Date
Date4 = "0.123"

Debug.Print Date1, Date2, Date3, Date4

End Sub

Use Debug.Print

Step 11: And to see the output, we will use immediate window as shown below. To access this, go to the View menu tab and select Immediate Window as shown below.

Select Immediate Window

Step 12: Now run the code by pressing the F5 key or by clicking on Play Button. We will see, date data type has given us the output but it is not in standard data format.

VBA CDate Example 2-11

Step 13: To get the standard data out, we will use CDate here as well. So, we will assign CDate for each date and time which we used for different variables as shown below.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = CDate("12345")

Dim Date2 As Date
Date2 = CDate("12/3/45")

Dim Date3 As Date
Date3 = CDate("00:10:00")

Dim Date4 As Date
Date4 = CDate("0.123")

Debug.Print Date1, Date2, Date3, Date4

End Sub

VBA CDate Example 2-12

Step 14: Now run the code by pressing the F5 key or by clicking on the Play Button.

VBA CDate Example 2-13

We will see the output of both Date and CDate are the same but there is basic common difference between both of them. And that is, CDate can convert any type of numbers into standard date format.

Step 15: Let’s try any text or alphabet with CDate and see what we get. So we have entered some random text as abc in variable Date4.

Code:

Sub VBA_CDate2()

Dim Date1 As Date
Date1 = CDate("12345")

Dim Date2 As Date
Date2 = CDate("12/3/45")

Dim Date3 As Date
Date3 = CDate("00:10:00")

Dim Date4 As Date
Date4 = CDate("abc")

Debug.Print Date1, Date2, Date3, Date4

End Sub

VBA CDate Example 2-14

Step 16: Now run the code again. We will get a message box with an error message as Type Mismatch. This is because CDate cannot read and convert text into a standard date and time format.

Type Mismatch Error

Pros & Cons of Excel VBA CDate Function

  • This can convert any date or time to standard format as required.
  • VBA CDate interpret any number as Date value and later convert that into a standard format.
  • It cannot interpret and convert the text into date format.

Things to Remember

  • CDate can only consider numbers as input but that number can be in any format.
  • The text value cannot be converted.
  • If we feed a time or date which is already in the standard format then it will again return the same value as output.
  • Date and CDate function work in the same manner. Whereas by CDate we can convert both time and date.

Recommended Articles

This is a guide to VBA CDate Function. Here we discuss how to use CDate Function in Excel using VBA Code along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Date Format
  2. Excel DATEDIF Function
  3. VBA DateSerial
  4. VBA DateDiff

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Web Development & many more.

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA

Download VBA CDate Excel Template

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW