YmdHms to Native Excel Date/Time

[DAG_P6]

For years, I've happily used the native YmdHms date format in my code, and even output dates in that format. More recently, I've been using Microsoft Excel, a lot, to store and analyze test results, which include log files and directory catalogs that contain various file time stamps. Everything is great, if all the inputs are YmdHms.

However, things don't go nearly so well when you start mixing YmdHms dates and native Excel dates, because the cannot be compared.

Several months ago, I developed a native worksheet formula to convert a string containing a YmdHms date into a native Excel date. As you can imagine, it's pretty hideous, and hard to use repeatedly, because it calls for repetitious, precise input of the source cell address six times.

Code Select Expand

=DATE(MID(B2,1,4),MID(B2,6,2),MID(B2,9,2))+TIME(MID(B2,12,2),MID(B2,15,2),MID(B2,18,2))

Even if you can fill the sheet by copying the formula around, you must get it into the first cell the hard way.

Today, I decided that I'd had enough, and I wrote a VBA function to do the same thing with a much simpler signature. Using the new function, the line shown above reduces to the following.

Code Select Expand

=YmdHmsToOADate(B2)

Not surprisingly, the VBA function, though
hideous, can be expressed in exactly four lines, as follows.

Code Select Expand


Public Function YmdHmsToOADate(ByRef pstrYmdHmsDate As String) As Variant
    If Len(pstrYmdHmsDate) > 0 Then
        YmdHmsToOADate = CDate(Mid(pstrYmdHmsDate, 1, 4) & "/" & Mid(pstrYmdHmsDate, 6, 2) & "/" & Mid(pstrYmdHmsDate, 9, 2) & " " & Mid(pstrYmdHmsDate, 12, 2) & ":" & Mid(pstrYmdHmsDate, 15, 2) & ":" & Mid(pstrYmdHmsDate, 18, 2))
    End If  ' If Len(pstrYmdHmsDate) > 0 Then
End Function


Attached are the above function, as a standard VBA module, YmdHmsToOADate.BAS, and the proof of concept workbook, YmdHmsToOADate.XLSB, which demonstrates the native worksheet function and the VBA shortcut.

Since I am giving it away, the VBA project is neither signed, nor locked.

Enjoy!

[stanl]

What about ObjectType("DATE",winbatch_date)?

[DAG_P6]

What about it? How does a WinBatch construct help me inside Excel workbooks.

Although the output is from a WinBatch script, my point is that I've imported the output into Microsoft Excel, and I am working in Excel, by itself, at that point. My point was simply that the formula and function simplify using Excel to process WinBatch dates.

Obviously, I have other options, such as FormatYmdHMSP6C, in my WWWILMuscle extender, but I am sometimes dealing with data that I either don't want to transform, or haven't gotten around to doing so.

[stanl]

Although the output is from a WinBatch script, my point is that I've imported the output into Microsoft Excel, and I am working in Excel, by itself

My bad. So  once imported WB no longer is associated with Excel. That wasn't clear in your initial post, hence my reply. My experience is to format output data as recordsets (from either files, using LogParser, or queries from DB Servers) then either using copyfromrecordset(), getrows(), or getstring() to format for Excel.

[DAG_P6]

Although the output is from a WinBatch script, my point is that I've imported the output into Microsoft Excel, and I am working in Excel, by itself

My bad. So  once imported WB no longer is associated with Excel. That wasn't clear in your initial post, hence my reply. My experience is to format output data as recordsets (from either files, using LogParser, or queries from DB Servers) then either using copyfromrecordset(), getrows(), or getstring() to format for Excel.

That is correct. Excel doesn't even enter the picture as part of the WB script, itself. The role of Excel is limited to after the fact analysis of script output. Since these analyses are geared towards either debugging or documenting a design issue, the outputs are typically neither permanent, nor normal, program outputs. For regular review and reporting, dates get the ReformatYmdHms treatment, in which a simple format string, perfected a decade or more ago, makes the time stamps comprehensible to Excel, so that they import as dates.

Though not strictly a WinBatch scripting matter, I thought that others, possibly including you, might have a use for it, from time to time. Besides that, it was an interesting exercise in data transformation.