Quick Jump
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.

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

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 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

Comments

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 wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


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.