Stripping the date portion of date/time in excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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!
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
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.
 
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.
 

jimr381

Jim
Joined
Jul 20, 2007
Messages
4,193
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.
 
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. :)
 
Joined
Jul 1, 2005
Messages
8,546
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. :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top