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

    Remove data in cell After certain point (2003)

    Greetings,

    I have a date field that looks like the following:

    2005/04/27 11:51:48:451


    This is imported from a file, and I really do not need the Time portion. Is there an easy method to remove the time portion? I am thinking of perhaps a trim function? Is this a "Len" opportunity?


    Thanks,
    Brad

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

    Re: Remove data in cell After certain point (2003)

    If you're only interested in removing the time portion from the display, set the number format of the cell(s) to a date format.

    If you want to physically remove the time portion, select the cell(s) and run this macro:

    Sub TruncateTime()
    Dim oCell As Range
    For Each oCell In Selection
    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: Remove data in cell After certain point (2003)

    Hans,

    I selected the cells, but I get a type mismatch, and the macro stops at the :

    oCell = Int(oCell) line.



    Brad

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

    Re: Remove data in cell After certain point (2003)

    That means the cells contain text values, not real date/time values. Try this version:

    Sub TruncateTime()
    Dim oCell As Range
    For Each oCell In Selection
    oCell = DateValue(Left(oCell, InStr(oCell, " ") - 1))
    Next oCell
    Selection.NumberFormat = "mm/dd/yyyy"
    End Sub

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

    Re: Remove data in cell After certain point (2003)

    That did it...


    Thanks!

    Now to digest the code....



    Brad

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

    Re: Remove data in cell After certain point (2003)

    <img src=/S/munch.gif border=0 alt=munch width=19 height=17>

Posting Permissions

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