Vbscript Adodb Read Excel Spreadsheet Date With Day of the Week
Introduction | Example | Tutorial | Applications
Introduction - CDate
This VBA tutorial shows y'all how to convert a data type from a string to a engagement with the VBA Type Conversion function CDate. Once converted to a date, you'll be able to employ date arithmetic and utilize a diverseness 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 ane: Convert string to integer
Part 2: Convert number to string
Example - CDate
Convert a Cord to a Date
Sub DemoCDate () 'Convert a data type to a engagement 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 Mistake GoTo 100 : ConvertToDate = CDate ( v1 ) Exit Role 100: MsgBox "Failed to convert "" " & v1 & " "" to a date." , , "Aborting - Failed Conversion" End Finish Function Make powerful macros with our free VBA Developer Kit
It'south piece of cake to copy and paste a macro similar this, merely it's harder brand 1 on your own. To aid you make macros like this, we built a free VBA Developer Kit and wrote the Big Volume of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more than - grab your free re-create below.
Tutorial - CDate
The function ConvertToDate does all the fun piece of work in this VBA tutorial. You lot call the function and pass it a cord or long integer data type representing the serial equivalent of the date. In the procedure DemoCDate, I chose to pass it a string simply I could have passed a long. For example, I could have used the command vDate = ConvertToDate(41680) to yield a engagement in the format "2/10/2014."
If the conversion to a engagement succeeds, your information blazon will exist changed to a variant with subtype "Appointment" and you can use it however you lot please. You can add dates, decrease dates, check to see what twenty-four hour period 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 upwardly letting you know it failed and your macro execution volition abort.
Convert a Cord in yyyymmddhhmmss Format to a Appointment
Information technology's pretty mutual for people to embed DTS (date/time stamps) into strings, like file names. If yous have a DTS in the format yymmddhhmmss or yyyymmddhhmmss, have no fearfulness! You can convert a string in this format to a appointment date type with the following macro:
Sub yyyymmddhhmmss_cdate () 'Catechumen a cord in yymmddhhmmss or yyyymmddhhmmss Dim ddate Every bit Date Dim sTime Equally String sTime = "20160704115959" ddate = CDate ( Format $ ( sTime , "00/00/00 00:00:00" )) End Sub The dollar sign ($) in the Format performance merely says to return the result of the Format operator as a string - information technology's largely unnecessary in this context, only I included information technology anyway. This VBA macro successfully converts the string to a appointment:
Converting strings to dates in VBScript
The CDate function works perfectly fine in VBScript, but I'thou going to go ahead and warn you. If you need to convert a string in the yyyymmddhhmmss engagement/time stamp format to a appointment using VBScript, the to a higher place macro will NOT piece of work since VBScript has no Format() operation. Instead, y'all'll demand to pass your cord to the following VBScript-uniform function:
Public Office 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 And then 'yymmddhhmmss dS = DateSerial ( CInt ( Left ( sDatetimestamp , 2 )), CInt ( Mid ( sDatetimestamp , 3 , 2 )), CInt ( Mid ( sDatetimestamp , 5 , 2 ))) dT = TimeSerial ( CInt ( Mid ( sDatetimestamp , seven , 2 )), CInt ( Mid ( sDatetimestamp , 9 , two )), CInt ( Mid ( sDatetimestamp , eleven , 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 , ii ))) dT = TimeSerial ( CInt ( Mid ( sDatetimestamp , 9 , 2 )), CInt ( Mid ( sDatetimestamp , eleven , 2 )), CInt ( Mid ( sDatetimestamp , xiii , 2 ))) dtm = CDate ( CStr ( dS ) + " " + CStr ( dT )) Else MsgBox "Invalid DTS Format" Finish End If Conv2Datetime = dtm End Function I found myself having to do a VBScript string to appointment conversion the other day, and this function worked like a amuse.
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. Yous can check the differences between the dates by subtracting them and you can utilize worksheet functions similar WEEKDAY() or WEEKNUM() to decide what day of the week or calendar 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 blazon conversion functions. If you lot're looking for more than string conversion articles, check out my tutorials on
- VBA strConv
- VBA strComp
- VBA CStr
- VBA CInt
For more VBA tips, techniques, and tactics, subscribe to our VBA Insiders email series using the grade beneath.
Ready to do more than with VBA?
We put together a giant PDF with over 300 pre-built macros and we want you to have it for free. Enter your email address below and we'll send you a re-create along with our VBA Developer Kit, loaded with VBA tips, tricks and shortcuts.
Source: https://wellsr.com/vba/2015/excel/vba-convert-string-to-date-with-cdate/
0 Response to "Vbscript Adodb Read Excel Spreadsheet Date With Day of the Week"
Post a Comment