Convert date looking values to Date format

[kwagner1]

Greetings,

I have data in COL H that for the most part represents dates (it is stored as text). I want another column (say... COL K) to have a formula that will convert any of the "date looking" values into a date string in "YYYY.MM.DD" format.... any suggestions on an IF or MID or LEFT, etc.... type formula that I could use?? If the value is not "date looking" it should remain (i.e. blank cells, "Production")

Here is some sample data (COL H):
E11.05.2010
12.10.2010
Production
06.11.2011
E11.05.2010


Here is the result I am looking for (COL K):
2010.11.05
2010.12.10
Production
2011.06.11
2010.11.05


Thanks!

 

[John_w]

Try this user-defined function (UDF):

Public Function CVDate(cellValue As String) As String
    Dim parts As Variant
    CVDate = cellValue
    parts = Split(cellValue, ".")
    If UBound(parts) = 2 Then
        If Val(parts(0)) = 0 Then parts(0) = Mid(parts(0), 2)
        CVDate = parts(2) & "." & parts(1) & "." & parts(0)
    End If
End Function

Use it like a formula in a cell:
=CVDate(H1)

 

[kwagner1]

thanks John - i'll check it out!!