extract time from date/time for sorting

**always Stumped** · 06-07-2011, 01:08 AM

Hi all,

Have a data dump that puts the date/time in a cell together (5/23/11 12:41 AM), which I need in one place so that everything is in order of the actual time it came in, but I also want to run a chart based on the time of day (hour) disregarding the dates. Even if it's formatted as HH:MM only, it still sorts by day. How can I make the data sort by time, regardless of the date in the cell? I can add another column, but I don't know how to pull only the hh:mm info out of the date cell. Alternatively, is there a way to sort the data in a chart without sorting it in the generating table?

Thanks!

**JBeaucaire** · 06-07-2011, 01:27 AM

A1 - Date/Time
B1: =MOD(A1, 1) (formatted as time)

**always Stumped** · 06-07-2011, 01:32 AM

Originally Posted by JBeaucaire

A1 - Date/Time
B1: =MOD(A1, 1) (formatted as time)

perfect, thanks!

**exclguru** · 01-22-2015, 08:20 PM

I think this will be useful beyond this one problem. I am assuming your date is formatted like so: 01/01/2015 01:31:23 PM

First, insert 3 columns to the right of the column with date and time. (Right-click on the column letter and click "insert.") Do you have a "Data" tab? If not, you can right-click in the blank space of the ribbon, click on "customize the ribbon" and check the "data" button from the main tabs (on the right).

Then, select the column of data with the date and time. Then, click on the data tab on "text to columns" and that brings up a wizard. Leave delimited selected, and click next. Check only the box labeled “space” and deselect any other boxes that may be checked. Click on "Next" and "Finish." That will split the data into date column, time column, and AM/PM column. Then I changed the format of the time cells by right-clicking and clicking on format. I selected 24 hour days (i.e. military time) and sorted that way. As long as you "expand the data selection" the times will be sorted in order and it will keep the original date in the box next to it. Then you should be able to sort normally based on date, time or AM/PM, or custom sort so you have different layers to sort by, for instance, date then time.

Good luck!