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.
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 spreadsheets with our free VBA Developer's Guide It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
It’s easy to copy and paste a macro like this, but it’s harder make one on your own. I’ve made hundreds of pre-built macros like this one and I want to send them to you so you can master file I/O, arrays, strings and more.
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!
The following Watch window illustrates the data type conversion.
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.
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.