Converting fields to date in design view

**GeoGreco** · 03-19-2009, 11:53 AM

I need to convert data that looks like this 08162008 it's formated as text and needs to look like this 08/16/2008 there are multiple dates and years within this field.

**GuruWannaB** · 03-19-2009, 12:04 PM

Try this - where a1 has your text format date data.

=DATE(RIGHT(A1,4),LEFT(A1,2),mid(a1,3,2))

**ExlGuru** · 03-25-2009, 12:29 AM

Try this:

Public Function TextBoxDateFormat(DAscii As Integer, DTBox As TextBox)
    
    'Set error trapping on
     On Error GoTo ErrorHandler
     
    'Inserts '/' into date
     TextBoxDateFormat = DAscii
    
    'Check code
     If IsNumeric(Chr(DAscii)) Then
         
         'Format
          If ((Len(DTBox.Text) = 2) Or (Len(DTBox.Text) = 5)) And (IsNumeric(Chr(DAscii))) Then
                  
              'Add a /
               DTBox.Text = DTBox.Text + "/"
               DTBox.SelStart = Len(DTBox.Text)
          
          ElseIf Len(DTBox.Text) = 7 Then
              
              'Format
               DTBox.Text = Format(DTBox + Chr(DAscii), "dd/mm/yyyy")
               TextBoxDateFormat = 0
               DTBox.SelStart = 10
          End If
     
     ElseIf DAscii <> 8 Then
     
         'Do not allow the keystroke
          TextBoxDateFormat = 0
     End If
    
    'Exit
     Exit Function
     
ErrorHandler:
    
    'Process error
     'WriteToErrorLog "ModHelperRoutines", "TextBoxDateFormat", Err.Number, Err.Description
     Resume Next

End Function