Hi,
In the attached sheet, one macro is developed which is not working properly. Can anyone help me.
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 works only for row number 11, 15 and 16. can anyone have a look on it
ub date1()
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
Bookmarks