Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date Conversion (2003)

    Greetings,
    I have the following macro, to strip the time format from a column of data.

    The data initially looks like this:
    2005/03/14 14:32:55:356
    2005/04/18 15:05:48:288
    2005/05/08 19:56:41:465
    2005/05/16 02:48:05:649
    2005/05/17 17:37:32:297
    2005/05/18 02:25:46:224
    2005/05/24 13:29:00:372
    2005/06/22 09:17:00:175
    2005/07/29 12:33:17:011
    2005/08/04 15:50:44:001
    2005/10/10 14:46:42:694
    2005/12/09 13:58:30:565
    2005/12/21 17:24:32:170


    End result needs to be in the M/D/YYYY format. The macro does what I expect, except the date. After the conversion IF I double click on one of the dates, it will them convert. any suggestions?

    Thanks, BRad



    Sub Deletesuffix()
    Columns("Z:Z").Select
    Selection.Insert Shift:=xlToRight
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "a"
    Range("Y1").Select
    Range("Y1").CurrentRegion.SpecialCells(xlCellTypeB lanks).FormulaR1C1 = "=LEFT(RC[-1],FIND("" "",RC[-1])-1)"
    Range("Z2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("Y2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    Columns("Z:Z").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date Conversion (2003)

    Try this. You can replace Selection with a specific range.

    Sub DeleteSuffix()
    Dim oCell As Range
    For Each oCell In Selection.Cells
    oCell = Int(oCell)
    Next oCell
    Selection.NumberFormat = "mm/dd/yyyy"
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date Conversion (2003)

    Hans,
    Thanks. Now I am not quite sure what to do.

    1) does this replace my entire macro?
    2) I do not understand how to do as you suggest.

    BRad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date Conversion (2003)

    It is intended to replace your entire macro, although I must admit that I tried to guess what your macro intended to do instead of what it actually does.

    Instead of Selection, you can use Range("Y1:Y100") or something like that.

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date Conversion (2003)

    Ok.. In my simplistic understanding, here is what I did:

    Sub DeleteSuffix()
    Dim oCell As Range
    For Each oCell In Range("Y1:Y300")
    oCell = Int(oCell)
    Next oCell
    Selection.NumberFormat = "mm/dd/yyyy"
    End Sub


    I get the following error:

    Run-time error 13 Type mismatch.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date Conversion (2003)

    Do all cells in range Y1:Y300 contain dates? If not, you'll have to add a test:

    Sub DeleteSuffix()
    Dim oCell As Range
    For Each oCell In Range("Y1:Y300").Cells
    If IsDate(oCell) Then
    oCell = Int(oCell)
    oCell.NumberFormat = "mm/dd/yyyy"
    End If
    Next oCell
    End Sub

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date Conversion (2003)

    Well......

    I ran the macro, but nothing changed.


    What now?


    Brad

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: date Conversion (2003)

    I have no idea what your workbook looks like, so how should I know?

  9. #9
    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: date Conversion (2003)

    Just a guess to the problem:
    The code won't change anything that is not a date. To be a date, the cell must have a number in it and the cell must be explicitly formatted as a date.

    Steve

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: date Conversion (2003)

    Hi Brad,

    Try this on your selected range:
    Sub DateIt()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Value = Left(oCell.Text, InStr(oCell.Text, " "))
    Next
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    US
    Posts
    439
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date Conversion (2003)

    Hey folks!

    I was out on a mini vacation. I worked my way through the latest suggestion and that did the trick. I appreciate all the help, as my solution was a mess.

    Thanks again,

    Brad

Posting Permissions

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