Introduction | Example | Tutorial | Applications | Comments
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
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 larger unnecessary in this context, but I included it anyway. This VBA macro successfull 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
Share this article with a friend if you like what you see and let me know how you plan on using the VBA script by leaving a comment below! Follow me on Google+ for more great VBA content.
Get these macros delivered right to your spreadsheet for free
Access tutorials and import my macros without ever leaving ExcelShow me - It's free
About Ryan Wells
Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.Follow