PDA

View Full Version : [SOLVED] Macro not working for partial date format conversion



abraham30
04-13-2014, 02:01 PM
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
04-14-2014, 03:32 AM
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 cnote: 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
04-14-2014, 05:34 AM
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
04-14-2014, 06:17 AM
sorry, i misread the required format, glad you could fix