1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Stripping the date portion of date/time in excel

Discussion in 'Business Applications' started by thedewman6, Jan 9, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. thedewman6

    thedewman6 Thread Starter

    Joined:
    Feb 28, 2007
    Messages:
    27
    Hello, I have spreadsheet with a number of dates and times ie (11/19/07 8:57 AM) in column A and data going along with each of these dates and times from columns B-CK. I am trying to limit the list to show the information that is for all times after 2:45PM. The problem is I cannot sort by time and the dates attached to all the times span from 2004-2007. Please help!
     
  2. Sponsor

  3. jimr381

    jimr381 Jim

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    If you format it as a time instead of a date in time you can paste it into Word and then paste it into the column adjacent to the original one.
     
  4. rconverse

    rconverse

    Joined:
    Sep 7, 2007
    Messages:
    191
    You could also insert two new columns. Copy the time/date column and paste special | value into the first new column. Then goto Data | Text to columns and you can separate the data into two separate columns and then delete the columns you don't need.
     
  5. jimr381

    jimr381 Jim

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    Value pastes the date number if I recall like 38800. ;) I tried that at first. If I missed something please feel free to clarify rconverse.
     
  6. rconverse

    rconverse

    Joined:
    Sep 7, 2007
    Messages:
    191
    That method worked for me, so not sure exactly.

    I formatted my column A to mm/dd/yy hh:mm am. Then I inserted some random dates and followed my steps above. The only thing that I had to do was then reformat column A to just date (no time). It didn't change the dates to a numeral.

    If the date in column A is not just formatted, but hard text, then the text to columns should certainly work, I think. :)
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,423
    Use this formula in a helper column:

    =A1-INT(A1)

    INT(A1) will give you just the date, so A1 (date and time) minus INT(A1) (just the date) will give you just the time.

    After you reformat the results as Number (2 DPs), you'll get (e.g.) 0.37 for 08:57 -- which is correct, since 8 hours 57 mins is a bit more than a third of a day.

    Then you can sort by the helper column. :)
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/669898