Macro not working for partial date format conversion

[abraham30]

Hello Everyone!

I took the help of some forum but not able to get answer. Can anyone help me.

In the attached sheet, one macro was developed which is not working properly for partial date.

My req is to convert date from dd-mon-yyyy to yyyy/mm/dd format. (23-jan-2010--> 2010/01/23)

If incomplete date is there, then hyphen should place the gap i.e.

For date like APR-2014------> Output should be 2014/04/--
For date like 1998 -----> Output should be 1998/--/--

The below macro does not work for all rows except row number 11, 15 and 16

Sub xx()
Dim c As Range, sNF
With Sheet1
    For Each c In Range("J2:J" & .Cells(Rows.Count, 10).End(xlUp).Row)
        If c.Value <> "" Then
            sNF = c.NumberFormat
            c.NumberFormat = "@"
            Select Case sNF
                Case "dd-mmm-yyyy hh:mi:ss":     c.Value = Format(c.Value, "yyyy/mm/dd")
                Case "dd-mmm-yyyy":     c.Value = Format(c.Value, "yyyy/mm/dd")
                Case "mmm-yyyy":        c.Value = Format(c.Value, "yyyy/mm/--")
                Case "General":         c.Value = c.Value & "/--/--"
            End Select
        End If
    Next c
End With
End Sub

[westconn1]

you can test this to see if it works for you

    For Each c In Range("J2:J" & Cells(Rows.Count, 10).End(xlUp).Row)
        If c.Value <> "" Then
            If Not IsDate(c.Text) Then
                    c.Value = Format("1/1/" & c.Text, "--/--/yyyy")
                Else
                Select Case Len(c.Text) - Len(Replace(c.Text, "-", ""))
                    Case 2
                        c.NumberFormat = "dd/mm/yyyy"
                        c.Value = c.Text
                    Case 1
                        c.Value = Format("1/" & Replace(c.Text, "-", "/"), "--/mm/yyyy")
                End Select
                
            End If
        End If
    Next c

note: any valid date is taken as a number, whereas your custom formats will be taken as text, so will be left aligned in the cell

[abraham30]

Thank You westconn for your great help.

When I run the macro, its working fine but not able to convert the date from dd-mon-yyyy to yyyy/mm/dd format. (year should comes first, followed by month and day)
i.e. For 22-JAN-2013 Output should be 2013/01/22
For May-2012 Output should be 2012/05/--
For date like 2011 Output should be 2011/--/--

I changed the macro like below.

Sub xx()
Dim c As Range, sNF
With Sheet1
    For Each c In Range("J2:J" & Cells(Rows.Count, 10).End(xlUp).Row)
    If c.Value <> "" Then
        If Not IsDate(c.Text) Then
            c.Value = Format("1/1/" & c.Text, "yyyy/--/--")
        Else
            Select Case Len(c.Text) - Len(Replace(c.Text, "-", ""))
            Case 2
                c.NumberFormat = "yyyy/mm/dd"
                c.Value = c.Text
            Case 1
                c.Value = Format("1/" & Replace(c.Text, "-", "/"), "yyyy/mm/--")
            End Select
             
        End If
    End If
Next c
End With
End Sub

[westconn1]

sorry, i misread the required format, glad you could fix