Introduction | Example | Tutorial | Applications

Introduction - CDate

This VBA tutorial shows you how to convert a data type from a string to a date with the VBA Type Conversion function CDate. Once converted to a date, you’ll be able to use date arithmetic and apply a variety of VBA functions to the dates.

This is Part 3 of a new tutorial series showing you how to convert between VBA data types.
Part 1: Convert string to integer
Part 2: Convert number to string

Example - CDate

Convert a String to a Date

Sub DemoCDate()
'Convert a data type to a date
Dim strDate As String, vDate As Variant
strDate = "November 27, 2015"
vDate = ConvertToDate(strDate)
MsgBox vDate, , "Successful Conversion"
End Sub

Function ConvertToDate(v1 As Variant) As Variant
On Error GoTo 100:
 ConvertToDate = CDate(v1)
 Exit Function
100:
 MsgBox "Failed to convert """ & v1 & """ to a date.", , "Aborting - Failed Conversion"
 End
End Function

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

Tutorial - CDate

The function ConvertToDate does all the fun work in this VBA tutorial. You call the function and pass it a string or long integer data type representing the serial equivalent of the date. In the procedure DemoCDate, I chose to pass it a string but I could have passed a long. For example, I could have used the command vDate = ConvertToDate(41680) to yield a date in the format “2/10/2014.”

If the conversion to a date succeeds, your data type will be changed to a variant with subtype “Date” and you can use it however you please. You can add dates, subtract dates, check to see what day of the week the date is on and more!

VBA CDate convert string to date

The following Watch window illustrates the data type conversion.

VBA CDate convert string to date
Successful conversion from string to Variant/Date

Like my convert number to string demo, if the conversion fails, a message box will pop up letting you know it failed and your macro execution will abort.

Failed to convert VBA string to date

Convert a String in yyyymmddhhmmss Format to a Date

It’s pretty common for people to embed DTS (date/time stamps) into strings, like file names. If you have a DTS in the format yymmddhhmmss or yyyymmddhhmmss, have no fear! You can convert a string in this format to a date date type with the following macro:

Sub yyyymmddhhmmss_cdate()
'Convert a string in yymmddhhmmss or yyyymmddhhmmss 
Dim ddate As Date
Dim sTime As String
sTime = "20160704115959"
ddate = CDate(Format$(sTime, "00/00/00 00:00:00"))
End Sub

The dollar sign ($) in the Format operation just says to return the result of the Format operator as a string - it’s largely unnecessary in this context, but I included it anyway. This VBA macro successfully converts the string to a date:

7/4/2016 11:59:59 AM

Converting strings to dates in VBScript

The CDate function works perfectly fine in VBScript, but I’m going to go ahead and warn you. If you need to convert a string in the yyyymmddhhmmss date/time stamp format to a date using VBScript, the above macro will NOT work since VBScript has no Format() operation. Instead, you’ll need to pass your string to the following VBScript-compatible function:

Public Function Conv2Datetime(sDatetimestamp)
'Format of sDatetimestamp:= yyyymmddhhmmss or yymmddhhmmss
'Required for VBScript since VBScript has no Format operation
Dim dtm
Dim dS
Dim dT

If Len(sDatetimestamp) = 12 Then 'yymmddhhmmss
    dS = DateSerial(CInt(Left(sDatetimestamp, 2)), CInt(Mid(sDatetimestamp, 3, 2)), CInt(Mid(sDatetimestamp, 5, 2)))
    dT = TimeSerial(CInt(Mid(sDatetimestamp, 7, 2)), CInt(Mid(sDatetimestamp, 9, 2)), CInt(Mid(sDatetimestamp, 11, 2)))
    dtm = CDate(CStr(dS) + " " + CStr(dT))
ElseIf Len(sDatetimestamp) = 14 Then 'yyyymmddhhmmss
    dS = DateSerial(CInt(Left(sDatetimestamp, 4)), CInt(Mid(sDatetimestamp, 5, 2)), CInt(Mid(sDatetimestamp, 7, 2)))
    dT = TimeSerial(CInt(Mid(sDatetimestamp, 9, 2)), CInt(Mid(sDatetimestamp, 11, 2)), CInt(Mid(sDatetimestamp, 13, 2)))
    dtm = CDate(CStr(dS) + " " + CStr(dT))
Else
    MsgBox "Invalid DTS Format"
    End
End If
Conv2Datetime = dtm
End Function

I found myself having to do a VBScript string to date conversion the other day, and this function worked like a charm.

Application Ideas - CDate

If you’re reading a bunch of dates from a text file or CSV file, the ability to convert them to dates is nice. You can check the differences between the dates by subtracting them and you can apply worksheet functions like WEEKDAY() or WEEKNUM() to determine what day of the week or week of the year the date falls on.

Once converted to a date, use the dates in all kinds of conditional statements and mathematical expressions.

The CDate Function is just one of several VBA data type conversion functions. If you’re looking for more string conversion articles, check out my tutorials on

For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the form below.