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.

`=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.

`=YmdHmsToOADate(B2)`

Not surprisingly, the VBA function, though

hideous, can be expressed in exactly four lines, as follows.

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