# Stripping the date portion of date/time in excel



## thedewman6 (Feb 28, 2007)

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 (Jul 20, 2007)

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.


----------



## rconverse (Sep 8, 2007)

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 (Jul 20, 2007)

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.


----------



## rconverse (Sep 8, 2007)

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.


----------



## bomb #21 (Jul 1, 2005)

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.


----------

