459,695 Members | 1,651 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,695 IT Pros & Developers. It's quick & easy.

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

 Expert 2.5K+ P: 2,951 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. Jan 28 '15 #1

#### ✓ answered by jforbes

You might try using Format to instead of Mid\$. Maybe something like this?:
Expand|Select|Wrap|Line Numbers
1. format("0127151532", "@@/@@/@@ @@@@")

7 Replies

 Expert 2.5K+ P: 2,951 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. Jan 28 '15 #2

 Expert Mod 2.5K+ P: 3,485 Seth, The trick is to look at each portion of the text string as a particular part of the date/time. So, 0127151532 becomes..... Expand|Select|Wrap|Line Numbers 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.... Expand|Select|Wrap|Line Numbers 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! Jan 28 '15 #3

 Expert 100+ P: 1,107 You might try using Format to instead of Mid\$. Maybe something like this?: Expand|Select|Wrap|Line Numbers format("0127151532", "@@/@@/@@ @@@@") Jan 28 '15 #4

 Expert 100+ P: 1,107 Oh, and I think you can use Mod to convert to 12 hour format: Expand|Select|Wrap|Line Numbers (1532 mod 1200) and (332 mod 1200) both return 332 Jan 28 '15 #5

 Expert 2.5K+ P: 2,951 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. Expand|Select|Wrap|Line Numbers CDate(Format("0127151532", "@@/@@/@@ @@:@@")) Jan 28 '15 #6

 Expert Mod 2.5K+ P: 3,485 @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! Jan 28 '15 #7

 Expert 100+ P: 1,107 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 =) Expand|Select|Wrap|Line Numbers 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   Jan 28 '15 #8