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.

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.

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.

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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.

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.

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

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

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

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.

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