+ Reply to Thread
Results 1 to 4 of 4

Sorting Time of Day in Column

  1. #1
    Registered User
    Join Date
    12-10-2007
    Posts
    5

    Sorting Time of Day in Column

    Okay, I've been able to surpass one obstacle, thanks to this board. Now I have another question. Now that I have two columns, one containing the date and one the time, how can I put them into order. I've tried the sort function, but the times are not moving accordingly. If I select everything, the dates move accordingly, but the times are then not in their appopriate place. Here is a small example of what my chart looks like:

    1/15/2007 01:08:57PM
    1/15/2007 02:37:54PM
    1/15/2007 02:42:59PM
    1/15/2007 07:42:22AM
    1/15/2007 07:47:03AM
    1/15/2007 10:02:39AM
    1/15/2007 10:02:39AM

    As you can see, the date is fine, but I need the times to be in order. Right now it recognizes 1:08pm to be the earliest in the day. How can I adjust? I hope I'm clear. Thank you.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    If the times are Excel dates rather than text, just sort by columns A and B. If they aren't, make them so; in C1 and copy down,

    =LEFT(B1,8) + 0.5 * (RIGHT(B1,2)="PM")

    Better would be to add the time to the date, and then just sort the single column.
    Last edited by shg; 12-11-2007 at 02:17 PM.

  3. #3
    Registered User
    Join Date
    12-10-2007
    Posts
    5
    I'm not sure if I understand. I'm not an expert in excel. Can you explain in plain language ?

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    1. If the dates in your example are in A1:A7, and the times in B1:B7, enter the formula in my prior post in C1 and copy down to C7.

    2. Copy C1:C7, then do Edit > Paste Special > Values.

    3. Copy C1:C7 again, select A1, and do Edit > Paste Special > Add.

    4. Format A1:A7 as dd/mm/yyyy hh:mm (or whatever date and time format you wish), and delete columns B and C. The dates and times are now in column A.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1