+ Reply to Thread
Results 1 to 5 of 5

Sorting treats dates as text

  1. #1
    Registered User
    Join Date
    02-02-2017
    Location
    Winnipeg, Canada
    MS-Off Ver
    MS Office 365 Plus - Version 16.0.6965.2117
    Posts
    3

    Sorting treats dates as text

    Hello!

    I have just installed Office 2016 (corporate installation) at work and now I have this problem:

    I run a report with my Quest Reporter (I think now it's known as Dell Reporter) to get a listing of Active Directory accounts. One of the attributes I need is the Last Logon Date.

    The format of the report is either Comma or Tab delimited (I've trued both and I get the same result as below) - so, a text file.

    I then proceed to open the text file in Excel, with one column showing all the dates in the following format (example):
    7/8/2016 10:43:16 AM
    4/6/2016 12:54:52 PM
    10/31/2016 8:32:59 AM

    When I sort that column, the values are taken as if they were text, therefore I get this result:
    10/31/2016 8:32:59 AM
    4/6/2016 12:54:52 PM
    7/8/2016 10:43:16 AM

    When I was expecting this:
    4/6/2016 12:54:52 PM
    7/8/2016 10:43:16 AM
    10/31/2016 8:32:59 AM

    Using the Right+Click, Format cells... and choosing another format for Date doesn't make any difference.

    I've also tried copy+Paste Special... all the different types of Paste : Same result.

    This never happened before - That report I run it monthly and no changes in the Quest Reporter or Active Directory have been performed, therefore the resulting data from that report is the same as before.

    Anybody have any idea how can I resolve this?

    Thanks!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    26,704

    Re: Sorting treats dates as text

    Treat it as a txt file, change the extension if necessary.
    Then when you open it with Excel choose the delimited option specifying the space and when you get to the part of the wizard that allows you to define the filed types select d/m/y for the date column.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    02-02-2017
    Location
    Winnipeg, Canada
    MS-Off Ver
    MS Office 365 Plus - Version 16.0.6965.2117
    Posts
    3

    Re: Sorting treats dates as text

    Hello, Richard!

    Your solution works as you state it, but (my bad) I should've specified that I have more than one column of data, therefore if I do it as you explain, I get the first column with the dates correctly, and the rest a total mess (lol).

    Any idea how to tackle something like this:

    (I want to be able to sort by either of the two columns that have dates+times)

    Creation Date Display Name Last Logon Timestamp Manager OU
    9/2/2016 12:47:07 PM AAAAAAA ZZZZZZ OU=PROG
    8/17/2015 3:19:35 PM BBBB 4/6/2016 12:54:52 PM YYYYYY OU=LIFE PROG
    8/16/2016 2:00:15 PM CCCCC XXXXXX OU=PROG
    7/11/2016 2:49:48 PM DDDD WWWWW OU=PROG
    6/30/2015 10:29:51 AM EEEE 9/6/2016 5:14:02 AM VVVVVV OU=Disabled Do Not Delete
    5/4/2016 9:05:31 AM FFFFF 9/19/2016 9:59:21 AM UUUUUUU OU=PROG


    I really appreciate your reply!

    Edit: BTW! I also opened the txt file as Tab delimited, then define the column types as d/m/y for both of the columns, but because they also have the Time, that didn't work, it still sorts them as text. Cheers!
    Last edited by Nuncio; 02-02-2017 at 01:25 PM. Reason: Added more info.

  4. #4
    Registered User
    Join Date
    02-02-2017
    Location
    Winnipeg, Canada
    MS-Off Ver
    MS Office 365 Plus - Version 16.0.6965.2117
    Posts
    3

    Re: Sorting treats dates as text

    Found the solution to this problem!

    Formats in Regional settings!

    Just go to Control Panel, Region, and set the format for the Short and Long Date to match what you have in Excel. For some reason (a lame reason, I would say) Excel cannot understand this is a date if it doesn't resemblance what Windows considers a date (regardless of you specifying the same format WITHIN Excel).

    So - that's it, I hope this helps someone who finds the same problem!

    Cheers.



    BTW - I have to give credit for this finding to my beautiful, beautiful supervisor whose beauty is only surpassed by her intelligence!

    Last edited by Nuncio; 02-02-2017 at 03:49 PM. Reason: Credits...

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,730

    Re: Sorting treats dates as text

    Thanks for sharing the solution with us
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel 2010 treats dates differently to excel 2013
    By HughTower in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 04-08-2015, 07:22 AM
  2. [SOLVED] Sorting just the text dates by macro
    By Ram_G in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-08-2015, 07:20 AM
  3. [SOLVED] Pivot table is sorting values with text first then dates?!?!?
    By leesrubberroom in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-09-2013, 07:19 AM
  4. [SOLVED] Sorting dates with added text issue.
    By skyping in forum Excel General
    Replies: 6
    Last Post: 11-26-2012, 09:38 PM
  5. Having issues sorting a column with dates and text
    By Honeybee129 in forum Excel General
    Replies: 3
    Last Post: 05-03-2007, 06:36 PM
  6. Paste special treats cells as a picture
    By charlie in forum Excel General
    Replies: 2
    Last Post: 11-17-2005, 12:10 PM
  7. [SOLVED] Unlinked Chart Treats Time Axis As Value - Any Fix?
    By Robert Smith in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 02-24-2005, 03:06 PM

Tags for this Thread

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