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
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