• Skip to primary navigation
  • Skip to content
  • Skip to primary sidebar
  • Skip to footer
EDUCBA

EDUCBA

MENUMENU
  • Resources
        • Excel Charts

          • Histogram Chart Excel
          • Basic Excel Formulas
          • Text to Columns in Excel
        • Excel Charts
        • Excel Tips

          • Excel Gantt Chart
          • IFERROR with VLOOKUP
          • Data Table in Excel
        • Excel Tips
        • Excel Tools in Excel

          • Stacked Column Chart
          • Cheat Sheet of Excel Formulas
          • Excel Data Validation
        • Histogram chart in excel
        • Others

          • Resources (A-Z)
          • Excel Functions
          • Financial Functions in Excel
          • Logical Functions in Excel
          • Lookup Reference Functions in Excel
          • Maths Function in Excel
          • TEXT and String Functions in Excel
          • View All
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course 1
        • All in One Bundle

          All-in-One-Excel-VBA-Bundle
        • Excel course

          Excel-Training
        • Others

          • Excel advanced course
          • VBA Course
          • Excel Data Analysis Course
          • Excel for Marketing Course
          • Excel for Finance Course
          • View All
  • 120+ Courses All in One Bundle
  • Login

VBA CDate

Home » Excel » Blog » VBA » VBA CDate

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.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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.

Popular Course in this category
VBA Training (3 Courses, 12+ Projects) 3 Online Courses | 13 Hands-on Projects | 45+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.7 (2,695 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 21+ Projects)Excel Data Analysis Training (12 Courses, 6+ Projects)

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. Excel DATEDIF Function

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar
Excel Functions Tutorials
  • VBA
    • VBA Max
    • VBA Environ
    • VBA Not
    • VBA LOOKUP
    • Programming in Excel
    • VBA Name Worksheet
    • VBA ScreenUpdating
    • VBA Do Loop
    • VBA Declare Array
    • VBA Macros
    • VBA Delete Sheet
    • VBA Editor
    • VBA Error Handling
    • VBA Goal Seek
    • VBA DateDiff
    • VBA On Error Resume Next
    • VBA Examples
    • VBA Counter
    • VBA Val
    • VBA Me
    • VBA Double
    • VBA Variable Types
    • VBA FreeFile
    • VBA Switch Case
    • VBA Unprotect Sheet
    • VBA Format Number
    • VBA ReDim
    • VBA IF Statements
    • VBA DoEvents
    • VBA Public Variable
    • VBA Columns
    • VBA Cdate
    • VBA Selection Range
    • VBA Hide Columns
    • VBA COUNTA
    • VBA Insert Row
    • VBA Protect Sheet
    • VBA AutoFill
    • VBA IF Not
    • VBA GetObject
    • VBA Debug Print
    • VBA Collection
    • VBA Text
    • VBA Randomize
    • VBA Variant
    • VBA Selection
    • VBA Right
    • VBA Date Format
    • VBA Rename Sheet
    • VBA Paste Values
    • VBA InputBox
    • VBA Conditional Formatting
    • VBA Pause
    • VBA Pivot Table
    • VBA Message Box
    • VBA OFFSET
    • VBA ByRef
    • FileCopy in VBA
    • VBA StrConv
    • VBA SUB
    • VBA Boolean
    • VBA Global Variables
    • VBA Worksheets
    • VBA IIF
    • VBA Close UserForm
    • VBA Variable Declaration
    • VBA Join
    • VBA Dictionary
    • VBA Operators
    • VBA Charts
    • VBA For Each Loop
    • VBA FileDialog
    • VBA Format
    • VBA MID
    • VBA GetOpenFileName
    • VBA DatePart
    • VBA Code
    • VBA Option Explicit
    • VBA FileSystemObject (FSO)
    • VBA ASC
    • VBA UserForm
    • VBA Cells
    • VBA InStrRev
    • VBA Class Module
    • VBA Constants
    • VBA Length of String
    • VBA DateSerial
    • VBA StrComp
    • VBA Array Length
    • VBA Check File Exists
    • VBA Ubound
    • VBA CDBL
    • VBA Activate Sheet
    • VBA DateValue
    • VBA Borders
    • VBA Comment
    • VBA Intersect
    • VBA Workbook
    • VBA Concatenate
    • VBA Name
    • VBA Find and Replace
    • VBA Tutorial
    • VBA CSTR
    • VBA Save As
    • VBA Hyperlink
    • VBA Print
    • VBA Switch
    • VBA END
    • VBA Like
    • VBA Set
    • VBA excel programming
    • VBA Call Sub
    • VBA Object
    • VBA RoundUp
    • VBA ArrayList
    • VBA Named Range
    • VBA PowerPoint
    • VBA Block Comment
    • VBA OverFlow Error
    • VBA Insert Column
    • VBA Lcase
    • VBA List Box
    • VBA Delete File
    • VBA Clear Contents
    • VBA TextBox
    • VBA Font Color
    • VBA Range Cells
    • VBA INT
    • VBA UCASE
    • VBA Value
    • VBA Remove Duplicates
    • VBA Break for Loop
    • VBA Sleep
    • VBA Do Until Loop
    • VBA Union
    • VBA Long
    • VBA Copy Paste
    • VBA Data Types
    • VBA Delete Column
    • VBA Enum
    • VBA IsEmpty
    • VBA 1004 Error
    • VBA RegEx
    • VBA IsNumeric
    • VBA Paste
    • VBA Transpose
    • VBA Left
    • VBA Delete Row
    • VBA Integer
    • VBA Active Cell
    • VBA InStr
    • VBA Round
    • VBA Subscript out of Range
    • VBA Dim
    • VBA Replace
    • VBA Sort
    • VBA String
    • VBA Split
    • VBA Wait
    • VBA MOD
    • VBA Time
    • VBA TIMER
    • VBA While Loop
    • VBA Date
    • Excel VBA MsgBox
    • VBA IFError
    • VBA Color Index
    • VBA Match
    • VBA Case
    • VBA Arrays
    • VBA GoTo
    • VBA On Error
    • VBA Range
    • VBA Do While Loop
    • VBA Number Format
    • VBA Loops
    • VBA TRIM
    • VBA Find
    • VBA Select Case
    • VBA Else If
  • Excel Functions (10+)
  • Excel Tools (97+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (12+)
  • Lookup Reference Functions in Excel (30+)
  • Maths Function in Excel (39+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (20+)
  • Statistical Functions in Excel (55+)
  • Information Functions in Excel (4+)
  • Excel Charts (44+)
  • Excel Tips (195+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (14+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • VBA Training
  • Excel Advanced Training
  • Excel Data Analysis Training
Footer
About Us
  • Who is EDUCBA?
  • Sign Up
  •  
Free Courses
  • Free Online Excel Course
  • Free Vba Course
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training
Resources
  • Resources (A To Z)
  • Excel Charts
  • Excel Tips
  • Excel Tools in Excel
  • Excel Functions
  • Financial Functions in Excel
  • Logical Functions in Excel
  • Lookup Reference Functions in Excel
  • Maths Function in Excel
  • TEXT and String Functions in Excel
  • Date and Time Function in Excel
  • Statistical Functions in Excel
  • Information Functions in Excel
Apps
  • iPhone & iPad
  • Android
Support
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions

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

EDUCBA

Download VBA CDate Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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

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 Login

Forgot Password?

Let’s Get Started
Please provide your Email ID
Email ID is incorrect

Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More

Limited Period Offer - Limited Period Offer - All in One Excel VBA Bundle (120+ Courses, 500+ hours of Videos) View More