NEED TO CONVERT TEXT TO DATE (M/DD/YYYY) FORMAT

[asksign]

Hi All,

I have a text data like 21-08-2023, i need to convert as 8/21/2023, can any one help, tried text to column, formatting method but it doesnt work.

 

[ranman256]

format will only work if the field is date-type numeric.
you can use a function to chop up the string:

Public Function cvt2Date(ByVal pvVal)
Dim m, d, y

If IsNull(pvVal) Then Exit Function
If Len(pvVal) = 10 Then
  d = Left(pvVal, 2)
  m = Mid(pvVal, 4, 2)
  y = Right(pvVal, 4)
 
  cvt2Date = m & "/" & d & "/" & y
End If
End Function