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

How to convert text in DDMMMYYYY format to a date

P: 2
I have a legacy date stored in a text field in DDMMMYYYY format that I need to convert to a date in MM/DD/YYYY format in Access. Example, convert text stored as 10JAN2017 to date in 01/10/2017 format. How?
Sep 14 '17 #1

✓ answered by ADezii

  1. As long as you strictly conform to the DDMMYYYY Format, the following Code should do the trick.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fConvertDate(strDate As String) As Date
    2. Dim bytDay As Byte
    3. Dim intYear As Integer
    4. Dim strMonth As String
    5. Dim bytMonth As Byte
    6.  
    7. bytDay = Val(Left$(strDate, 2))
    8. intYear = CInt(Mid$(strDate, 6, 4))
    9. strMonth = UCase$(Mid$(strDate, 3, 3))
    10.  
    11. bytMonth = Val(Switch(strMonth = "JAN", "1", strMonth = "FEB", "2", strMonth = "MAR", "3", _
    12.                strMonth = "APR", "4", strMonth = "MAY", "5", strMonth = "JUN", "6", strMonth = "JUL", "7", _
    13.                strMonth = "AUG", "8", strMonth = "SEP", "9", strMonth = "OCT", "10", strMonth = "NOV", "11", _
    14.                strMonth = "DEC", "12"))
    15.  
    16. fConvertDate = DateSerial(intYear, bytMonth, bytDay)
    17. End Function
    18.  
  2. Sample Function Calls:
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print Format$(fConvertDate("10JAN2017"), "mm/dd/yyyy")
    2. Debug.Print Format$(fConvertDate("20dec2016"), "mm/dd/yyyy")
    3. Debug.Print Format$(fConvertDate("01FEB2017"), "mm/dd/yyyy")
    4. Debug.Print Format$(fConvertDate("15JuL2017"), "mm/dd/yyyy")
  3. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. 01/10/2017
    2. 12/20/2016
    3. 02/01/2017
    4. 07/15/2017

Share this Question
Share on Google+
8 Replies


P: 1
maybe this will help you a bit https://bytes.com/topic/javascript/a...-dd-dd-mm-yyyy
Sep 14 '17 #2

PhilOfWalton
Expert 100+
P: 1,116
Try

Expand|Select|Wrap|Line Numbers
  1.  Function ChangeDDMMMYYYYToDate(StrIn As String) As Date
  2.      'Print ChangeDDMMMYYYYToDate("10JAN2017"
  3.  
  4.         Dim i As Integer
  5.         Dim Dy As Integer
  6.         Dim Mnth As String
  7.         Dim Yr As Integer
  8.  
  9.         ' Extract Day
  10.         For i = 1 To Len(StrIn)
  11.             If Asc(Mid(StrIn, i, 1)) > 47 And Asc(Mid(StrIn, i, 1)) < 58 Then ' between 0 and 9
  12.                 Dy = Dy & Mid(StrIn, i, 1)
  13.             Else
  14.                 Exit For
  15.             End If
  16.         Next i
  17.  
  18.         ' Extract Month
  19.         For i = i To Len(StrIn)
  20.             If Asc(Mid(StrIn, i, 1)) > 64 And Asc(Mid(StrIn, i, 1)) < 91 _
  21.             Or Asc(Mid(StrIn, i, 1)) > 96 And Asc(Mid(StrIn, i, 1)) < 123 Then  ' between Uppercase A to Z and Lower case a to z
  22.                 Mnth = Mnth & Mid(StrIn, i, 1)
  23.             Else
  24.                 Exit For
  25.             End If
  26.         Next i
  27.  
  28.         ' Extract Year
  29.         For i = i To Len(StrIn)
  30.             If Asc(Mid(StrIn, i, 1)) > 47 And Asc(Mid(StrIn, i, 1)) < 58 Then
  31.                 Yr = Yr & Mid(StrIn, i, 1)
  32.             Else
  33.                 Exit For
  34.             End If
  35.         Next i
  36.  
  37.         ChangeDDMMMYYYYToDate = CDate(Dy & "/" & Mnth & "/" & Yr)
  38.  
  39.      End Function
  40.  
Because I am UK based, I can't check that the output format is in the American format (I get 10/01/2017)

Phil
Sep 14 '17 #3

ADezii
Expert 5K+
P: 8,584
  1. As long as you strictly conform to the DDMMYYYY Format, the following Code should do the trick.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fConvertDate(strDate As String) As Date
    2. Dim bytDay As Byte
    3. Dim intYear As Integer
    4. Dim strMonth As String
    5. Dim bytMonth As Byte
    6.  
    7. bytDay = Val(Left$(strDate, 2))
    8. intYear = CInt(Mid$(strDate, 6, 4))
    9. strMonth = UCase$(Mid$(strDate, 3, 3))
    10.  
    11. bytMonth = Val(Switch(strMonth = "JAN", "1", strMonth = "FEB", "2", strMonth = "MAR", "3", _
    12.                strMonth = "APR", "4", strMonth = "MAY", "5", strMonth = "JUN", "6", strMonth = "JUL", "7", _
    13.                strMonth = "AUG", "8", strMonth = "SEP", "9", strMonth = "OCT", "10", strMonth = "NOV", "11", _
    14.                strMonth = "DEC", "12"))
    15.  
    16. fConvertDate = DateSerial(intYear, bytMonth, bytDay)
    17. End Function
    18.  
  2. Sample Function Calls:
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print Format$(fConvertDate("10JAN2017"), "mm/dd/yyyy")
    2. Debug.Print Format$(fConvertDate("20dec2016"), "mm/dd/yyyy")
    3. Debug.Print Format$(fConvertDate("01FEB2017"), "mm/dd/yyyy")
    4. Debug.Print Format$(fConvertDate("15JuL2017"), "mm/dd/yyyy")
  3. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. 01/10/2017
    2. 12/20/2016
    3. 02/01/2017
    4. 07/15/2017
Sep 14 '17 #4

NeoPa
Expert Mod 15k+
P: 30,741
Let's be clear. The OP requested an output format of a date to be ... (whatever). The format doesn't matter as dates are not stored in formatted form. The output is a date. How that date is subsequently formatted is all but irrelevant. One can choose any of the possible formats quite easily once the data is stored as a date.

The code to the currently formatted date text in could be as simple as :
Expand|Select|Wrap|Line Numbers
  1. CDate(Left(X,2) & " " & Mid(X,3,3) & " " & Right(X,4))
X in this case represents the string you currently have the date data in.

This works in both VBA and SQL.
Sep 14 '17 #5

NeoPa
Expert Mod 15k+
P: 30,741
Vaiolet3:
maybe this will help you a bit
Thank you for your effort but that's unlikely to help someone working in Access - as it's a JavaScript thread. We appreciate all attempts to help of course.
Sep 14 '17 #6

P: 2
Thanks ADezii...that worked great!
Sep 16 '17 #7

ADezii
Expert 5K+
P: 8,584
You are quite welcome.
Sep 16 '17 #8

NeoPa
Expert Mod 15k+
P: 30,741
Is there a reason to go to all that trouble when CDate() recognises all the three character abbreviations for the months anyway? I hate to see such code used when the solution is so much simpler.
Sep 16 '17 #9

Post your reply

Sign in to post your reply or Sign up for a free account.