How do I convert a numeric string to a date value?

[Seth Schrock]

I am trying to import a text file into my database. One of the fields that I need is a Date/Time field in my database. My problem is that the value in the text file shows the date of 1/27/2015 15:32 as 0127151532. How do I convert this? I also need the 24 hour clock converted to a 12 hour clock at the same time.

[Seth Schrock]

I have found a possible solution using the DateSerial() and TimeSerial() functions and then use the CDate() function to convert the strings to a date. The problem is that I have to do a lot of Mid() functions to split out the pairs of numbers. If anyone has a better solution, I would love to hear it.

[twinnyfo]

Seth,

The trick is to look at each portion of the text string as a particular part of the date/time.

So, 0127151532 becomes.....

Left(strDateTime, 2) = ' the Month
Mid(strDateTime, 3, 2) = 'the day of the month
Mid(strDateTime, 5, 2) = 'the Year

Adding "/" between those values and you can use the CDate() Function to convert into a Date Value.

Likewise....

Mid(strDateTime, 7, 2) = ' the Hour
Mid(strDateTime, 9, 2) = 'the Minute

And then, adding ":" between the text values and using the CDate() function will generate a Time.

Or, you could put all the values together before applying the CDate() function.

Hope this hepps!

[jforbes]

You might try using Format to instead of Mid$. Maybe something like this?:

format("0127151532", "@@/@@/@@ @@@@")

[jforbes]

Oh, and I think you can use Mod to convert to 12 hour format:

(1532 mod 1200) and (332 mod 1200) both return 332

[Seth Schrock]

Thank-you jforbes. That is exactly what I need. Here is my final code to get it to work the way that I need it to.

CDate(Format("0127151532", "@@/@@/@@ @@:@@"))

[twinnyfo]

@jforbes,

Cool! I was not aware one could apply formats to strings using the Format() Function like that. Another tool I can put in my tool kit!

[jforbes]

Glad it helps.

I was working on a custom formatting function recently that is somewhat related to this. The Format() will insert characters into a string, but I needed something to only put them in if they weren't there already. I made the following big o' mess to do just that. I though I would share incase you guys might want to use it in the future, plus I like to share =)

Public Function isCharAlphaNumeric(ByRef sString As String) As Boolean
    isCharAlphaNumeric = (sString >= "A" And sString <= "Z") Or (sString >= "0" And sString <= "9")
End Function

Public Function formatPartNumber(ByRef sString As String, ByRef sTemplate As String) As String

    ' Use >, <, or ^ as the first character for
    '   > Uppercase
    '   < Lowercase
    '   ^ Camelcase (First letter of each word)
    ' Use ~ for any AlphaNumeric
    ' Use {} for an optional set where the start position is remembered and reset if a match doesn't occur
    '       Helpful for when there are multiple options at the end of a string
    ' Use [] for an optional set that doesn't reset the start postion
    '
    ' Examples
    ' --------
    ' formatPartNumber("aBCd-efg/ap", ">~~~~[-~~~]{/AP}{/S}") -> ABCD-EFG/AP
    ' formatPartNumber("aBCdefg/sXYz", ">~~~~[-~~~]{/AP}{/S}[~][~][~]") -> ABCD-EFG/SXYZ
    ' formatPartNumber("aBCd-efg", "<~~~~[-~~~]{/AP}") -> abcd-efg
    ' formatPartNumber("aBCd-efgxyz", "^~~~~[-~~~]{/AP}") -> Abcd-efg
    
    Dim sResult As String
    Dim sBuild As String
    Dim iTemplate As Integer
    Dim iString As Integer
    Dim iFormat As Integer
    Dim sCurrentTemplateChar As String
    Dim iOptionalStringPos As Integer
    Dim bOptional As Boolean
    Dim iOptionalMatch As Integer
    Dim sTemp As String
    
    iTemplate = 2
    iString = 1
    iFormat = 0
    
    Select Case Left(sTemplate, 1)
        Case ">" ' Upper
            iFormat = 1
        Case "<" ' Lower
            iFormat = 2
        Case "^" ' Camel Case
            iFormat = 3
        Case Else ' None
            iFormat = 0
            iTemplate = 1
    End Select
            
    Do While iTemplate <= Len(sTemplate)
        sCurrentTemplateChar = Mid(sTemplate, iTemplate, 1)
        Select Case sCurrentTemplateChar
            Case "~" ' Character
                sTemp = Mid(sString, iString, 1)
                sBuild = sBuild & Mid(sString, iString, 1)
                iString = iString + 1
                If iOptionalMatch > 0 Then If Not isCharAlphaNumeric(sTemp) Then iOptionalMatch = 0
            Case "{" ' Optional Match Start
                sResult = sResult & sBuild
                sBuild = ""
                bOptional = True
                iOptionalMatch = 1
            Case "}" ' Optional Match End
                If iOptionalMatch > 0 Then
                    sResult = sResult & sBuild
                Else
                    iString = iOptionalStringPos  ' Backup position
                End If
                sBuild = ""
                iOptionalMatch = 0
            Case "[" ' Optional Group Start
                sResult = sResult & sBuild
                sBuild = ""
                bOptional = True
                iOptionalMatch = 2
            Case "]" ' Optional Group End
                If iOptionalMatch <> 0 Then sResult = sResult & sBuild
                sBuild = ""
                bOptional = False
                iOptionalMatch = 0
            Case Else ' Other
                If bOptional Then
                    Select Case iOptionalMatch
                        Case 0
                        Case 1
                            If Mid(sString, iString, 1) <> sCurrentTemplateChar Then
                                iOptionalMatch = 0
                            Else
                                iString = iString + 1
                            End If
                        Case 2
                            sTemp = Mid(sString, iString, 1)
                            If Len(sString) < iString Then iOptionalMatch = 0
                            If sCurrentTemplateChar = "~" Or sTemp = sCurrentTemplateChar Then
                                iString = iString + 1
                            End If
                    End Select
                Else
                    If Mid(sString, iString, 1) = sCurrentTemplateChar Then iString = iString + 1
                End If
                sBuild = sBuild & sCurrentTemplateChar
        End Select
        If Not bOptional Then iOptionalStringPos = iString
        iTemplate = iTemplate + 1
    Loop
    
    ' Select case, haha, get it
    Select Case iFormat
        Case 1
            formatPartNumber = UCase(sResult)
        Case 2
            formatPartNumber = LCase(sResult)
        Case 3
            formatPartNumber = StrConv(sResult, 3)
        Case Else
            formatPartNumber = sResult
    End Select
End Function