Results 1 to 6 of 6

Macro not working properly for conversion of date format

Threaded View

  1. #1
    Registered User
    Join Date
    03-07-2013
    Location
    zurich
    MS-Off Ver
    Excel 2010
    Posts
    99

    Macro not working properly for conversion of date format

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro isn't working properly.
    By EAgerton in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2014, 01:14 AM
  2. Macro Not Working Properly
    By fasrose in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-17-2013, 04:49 PM
  3. Date Sort in Pivot Table - Not working properly
    By claudiocustodio in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 07-12-2013, 04:30 PM
  4. Replies: 1
    Last Post: 06-15-2012, 02:03 PM
  5. Macro Not Working Properly
    By PA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-07-2006, 11:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1