Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    make excel do dirty work (excel )

    I am stuck with this awful task and wondering if there is a possible formula that can rid me of this awful task

    I have a list of date 1000 of them. except they are not in a uniform format, is there a way to make them all in the same format?

    they are all in month/date,year format
    except some are nov/13/05 and the other 11/13/5
    and so on???

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make excel do dirty work (excel )

    Patrick

    Not the perfect solution but it does clean up a lot of your data.

    1) Press Alt+F11
    2) In the left hand pane RH mouse click add new module.

    In new module paste this:

    Function NewDate(dateval As Date)

    NewDate = Format(dateval, "mm/dd/yy")

    End Function


    Go back to your Worksheet and (say dates are in column A) in column B, select B1 write the function =NewDate(A1) and copy down. If you can delete the blanks in column A you have new dates as per your format request.
    Jerry

  3. The Following User Says Thank You to Jezza For This Useful Post:

    davefornet (2014-06-12)

  4. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: make excel do dirty work (excel )

    I think the easiest way would be do a series of find/replace and manual (there are so many different ways they are different a formula will not work, and it seems to complex to use a macro)

    Change / to , 20
    Get rid of periods
    Change , 2020 to , 20 (these come from #1
    Change to /
    Change ,2 to , 2
    Change " 200" to ", 200"
    Change ,, to , (comes from above)

    This should convert most. The numbers are dates, the text can be converted using datevalue. Some (apirl) and other date formats will have to be converted manually...

    Steve

  5. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make excel do dirty work (excel )

    So by formatting column A and replacing with / in column A and by using my function will in fact change the majority of the #Values <img src=/S/grin.gif border=0 alt=grin width=15 height=15> so a mish mash of of our methods would work <img src=/S/gent.gif border=0 alt=gent width=17 height=25>
    Jerry

  6. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make excel do dirty work (excel )

    I did the same thing as Steve, only in code. Note, he has some other good corrections that were not needed for your list, so they never got into the code. The revised list is attached with the macro. HTH --Sam
    <pre>Option Explicit

    Sub Macro1()
    Dim i As Long
    Dim c As Range, s As String, v As Variant

    ' Remove blanks
    For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If Cells(i, 1) = "" Then Rows(i).Delete
    Next i

    For Each c In ActiveSheet.UsedRange.Columns(1).Cells
    s = c.Text
    s = Replace(s, "", "/")
    s = Replace(s, ".", " ")
    s = Replace(s, "Apirl", "April")
    If UBound(Split(s, "/")) = 1 Then _
    s = Replace(s, "/", ",")
    For i = 1 To 9
    s = Replace(s, "Feb" & i, "Feb " & i)
    Next i
    For i = 1 To 9
    s = Replace(s, "May" & i, "May " & i)
    Next i
    For i = 1 To 9
    s = Replace(s, "June" & i, "June " & i)
    Next i

    If IsDate(s) Then
    c.FormulaR1C1 = CDate(s)
    c.Interior.ColorIndex = 0
    Else
    c.Interior.ColorIndex = 6
    End If
    Next c
    Columns("A:A").NumberFormat = "mmm dd, yyyy"
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #6
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make excel do dirty work (excel )

    thank a bunch Steve, Sam, Jerry
    Sam,
    does the blanks need to be removed?
    the dates are what i am trying to but in a data base and they belong to a group. some records dont have a date so thats why there are blanks throught the column??

  8. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make excel do dirty work (excel )

    >does the blanks need to be removed
    No, just delete that loop at the begining

    Was that the actual list? I just noticed that you said you had 11/13/5
    I don't think that will get corrected, but any remaining invalid dates will be filled with yellow. You may want to change the lines
    <pre> Else
    c.Interior.ColorIndex = 6
    End If
    </pre>

    to
    <pre> ElseIf c.Text <> "" Then
    c.Interior.ColorIndex = 6
    End If
    </pre>

    so that the blanks are not colored.
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #8
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: make excel do dirty work (excel )

    yes that was the actual list,

    when i said 11/13/5 i was giving an example of my problem

    thanks for your help

Posting Permissions

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