+ Reply to Thread
Results 1 to 17 of 17

Date & Time in the same column, needs sorting

  1. 03-20-2015, 02:11 PM #1
    bibu
    bibu is offline
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    303

    Date & Time in the same column, needs sorting

    Hello,
    I hope this one's going to be easy.
    Let's take the G column for example. The database I'm working with is generated automatically so I can't help but have the time and date in a single column. The problem is I need to sort the column chronologically or counter-chronologically,and Excel doesn't even see the cells as time or date, I've tried formatting the column but no luck. So I hoped there's an easy way around this. Keep in mind that the actual database is tens of thousands of rows, not sure it's relevant but..
    Thanks !
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-20-2015, 02:23 PM #2
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Date & Time in the same column, needs sorting

    Maybe I missed something, but, I was able to get it to sort by date/time both ways without any problems.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom
    Reply With QuoteRegister To Reply
  3. 03-20-2015, 02:25 PM #3
    Steveapa
    Steveapa is offline
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Office 365 MSO (16.0.10730.20264) 32-bit
    Posts
    160

    Re: Date & Time in the same column, needs sorting

    YO...

    I must not understand.. I downloaded your file. Clicked on "DT_ACTUAL_OFF", hit sort & filter - choose A to Z and it sorted it in chronological order... Format of the cells are "General". Looks to me like it is sorting alphabetically, which is Chronological.
    Reply With QuoteRegister To Reply
  4. 03-20-2015, 02:26 PM #4
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Date & Time in the same column, needs sorting

    This because your dates were entered as Text and needs to be converted to numbers. Click on any empty cell and copy it, highlight column with your dates, make a right-click and select Paste Special and select Add from Operations and Ok.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  5. 03-20-2015, 02:33 PM #5
    mcmahobt
    mcmahobt is offline
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Date & Time in the same column, needs sorting

    Select column G, select Data on the ribon, Text to Columns, select delimited, deselect tabs (or keep, doesn't matter), select next, change date to "DMY", select finish. Although some of your values are still stored as text as AlKey explained, you can sort based on ascending/descending values.
    Spread the love, add to the Rep

    "None of us are as smart as all of us."
    Reply With QuoteRegister To Reply
  6. 03-20-2015, 02:34 PM #6
    bibu
    bibu is offline
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    303

    Re: Date & Time in the same column, needs sorting

    You are right, in the sample sheet the sorting does work properly. I originally tried this in the main database and it does not work properly, so I thought it will also not work in the sample. Let me check a few things and I'll update.
    Reply With QuoteRegister To Reply
  7. 03-20-2015, 02:51 PM #7
    bibu
    bibu is offline
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    303

    Re: Date & Time in the same column, needs sorting

    I think the sample was too small. I will attach the large database. What I am trying to do is select all the visible cells, press the "Filter" button to filter by any column, and then I want to sort each of the "Date/Time" columns chronologically. This means that it should(more or less)start on February the 9th and end on March the 15th.
    It's too big so I've uploaded it on DropBox:
    https://dl.dropboxusercontent.com/u/...ar%202015.xlsx
    TY
    Last edited by bibu; 03-20-2015 at 02:55 PM.
    Reply With QuoteRegister To Reply
  8. 03-20-2015, 03:01 PM #8
    Steveapa
    Steveapa is offline
    Forum Contributor
    Join Date
    10-02-2013
    Location
    Harrisburg, PA
    MS-Off Ver
    Office 365 MSO (16.0.10730.20264) 32-bit
    Posts
    160

    Re: Date & Time in the same column, needs sorting

    I cannot get to dropbox from work... Sorry
    Reply With QuoteRegister To Reply
  9. 03-20-2015, 03:10 PM #9
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Date & Time in the same column, needs sorting

    You may have to make it a table to do what you are talking about. Go to insert, highlight the columns (A through P) click the table "icon" make sure the correct columns are listed and that the checkbox for headers is checked. Once this is done, you will have a "searchable" table.
    Last edited by gmr4evr1; 03-20-2015 at 03:11 PM. Reason: changed wording
    Reply With QuoteRegister To Reply
  10. 03-20-2015, 03:33 PM #10
    bibu
    bibu is offline
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    303

    Re: Date & Time in the same column, needs sorting

    gmr4evr1, the "Sort" button still works the same as before. I am not sure how to use the "search" facility to sort the date/time columns properly. Thanks
    Reply With QuoteRegister To Reply
  11. 03-20-2015, 03:48 PM #11
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Date & Time in the same column, needs sorting

    If you've made it into a table as above, click the down arrow of the column (G) you want to search (filter) by and select the date range(s) you would like to view
    Last edited by gmr4evr1; 03-20-2015 at 04:03 PM.
    Reply With QuoteRegister To Reply
  12. 03-20-2015, 04:16 PM #12
    bibu
    bibu is offline
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    303

    Re: Date & Time in the same column, needs sorting

    I understand. Just for the record, is there any way of sorting the entire column chronologically through the sort button? If there is not, then consider this issue solved, thanks
    Reply With QuoteRegister To Reply
  13. 03-20-2015, 04:27 PM #13
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Date & Time in the same column, needs sorting

    I wasn't able to download your file from dropbox, however, post #5 from mcmahobt, might get you in the right direction if the sort still isn't working and you haven't tried it yet.
    Reply With QuoteRegister To Reply
  14. 03-20-2015, 04:42 PM #14
    bibu
    bibu is offline
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    303

    Re: Date & Time in the same column, needs sorting

    I'm sorry, I'll attach another sample:
    If sorted chronologically it should show rows starting from 23 feb until 08 mar
    mcmahobt's suggestion does not seem to get the job done
    Attached Files Attached Files
    Last edited by bibu; 03-20-2015 at 04:46 PM.
    Reply With QuoteRegister To Reply
  15. 03-20-2015, 04:58 PM #15
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Date & Time in the same column, needs sorting

    OK, now I see. I'm not sure how to do that. Other then selecting the dates you want, but that is a lot to go through and select. I believe my idea is a bust and wont work in this situation.
    Reply With QuoteRegister To Reply
  16. 03-20-2015, 04:59 PM #16
    bibu
    bibu is offline
    Forum Contributor
    Join Date
    08-04-2013
    Location
    Bucharest
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    303

    Re: Date & Time in the same column, needs sorting

    Thanks for trying!
    Reply With QuoteRegister To Reply
  17. 03-21-2015, 07:49 PM #17
    gmr4evr1
    gmr4evr1 is offline
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Date & Time in the same column, needs sorting

    No problem
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. extract time from date/time for sorting
    By always Stumped in forum Excel General
    Replies: 3
    Last Post: 01-22-2015, 08:20 PM
  2. having trouble sorting a date column in a sheet with a column populated via a vlookup
    By Late Learner in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-28-2014, 12:36 PM
  3. [SOLVED] Need help sorting a field with date and time
    By mnhim001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-19-2013, 01:14 AM
  4. Sorting the Date and Time
    By qwert2001 in forum Excel General
    Replies: 4
    Last Post: 01-03-2013, 10:45 PM
  5. Sorting a dataset with another using date and time
    By Matt Guernsey in forum Excel General
    Replies: 1
    Last Post: 07-22-2010, 08:29 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1