close

Privacy guaranteed - Your email is not shared with anyone.

sorting data in excel

Discussion in 'Tech Talk' started by k9medic, Dec 22, 2010.


  1. k9medic

    k9medic
    Expand Collapse

    Joined:
    Sep 16, 2000
    1,437
    86
    Location:
    at an LZ near you
    I need to sort some date/time data into a graph in excel 2003.


    Basically the date looks like this in the cell

    12/14/2010 18:50

    I need to get rid of the date leaving only the time.

    any ideas how to do this?
     
  2. RottnJP

    RottnJP
    Expand Collapse
    Lifetime Member

    Joined:
    Feb 1, 2005
    1,945
    0
    Location:
    CT
    Try reformatting it to be just the time, first. Right click, and select "Format cells" and it'll tell you you have a custom format that includes date and hours and minutes. Select a format that "hh:mm"

    Try sorting on that. It may or may not work- Depends if it still uses the data you have formatted out of view. Probably won't work, but worth a try as a easy way to do it.

    If that doesn't work, you need to parse the data field.

    Select "Data" then "Text to columns"
    Select "Delimited" and then check the box for "space"

    Interestingly, the data field in my version of excel, 2003, acts like 12-hour time, even though its displayed as a 24-hour clock. So doing the above parses off a "PM" and turns the remaining time field into an "AM" Odd, and stupid. Shoudln't do that. See what your version of excel does.

    If it turns it into a morning time, we can add the "PM" back into the field you want, but I need to look up how do do that later- In the middle of something now. (it's called "concatenating") if you want to check the help menu.
     

  3. k9medic

    k9medic
    Expand Collapse

    Joined:
    Sep 16, 2000
    1,437
    86
    Location:
    at an LZ near you
    Awesome - It worked. thanks!
     
  4. RottnJP

    RottnJP
    Expand Collapse
    Lifetime Member

    Joined:
    Feb 1, 2005
    1,945
    0
    Location:
    CT
    Glad to hear it worked out easy for you!