+ Reply to Thread
Results 1 to 10 of 10

Dates in Excel Spreadsheet won't sort correctly.

  1. 05-06-2014, 01:11 PM #1
    rbdiesel
    rbdiesel is offline
    Registered User
    Join Date
    05-06-2014
    Location
    mitchellville
    MS-Off Ver
    Excel 2010
    Posts
    4

    Dates in Excel Spreadsheet won't sort correctly.

    Now I have never said I am an Excel expert but, I do know my way around it....or so I thought. I have been fighting with my spread sheet for a few days. The dates wont line up correctly.

    5/1/2014 2:15PM
    5/1/2014 @ 2:00 PM
    5/1/2014 @ 2:45 PM
    5/1/2014 @ 8:00 AM
    5/12/2014 @ 6:00 PM
    5/12/2014 @ 6:15 PM
    5/12/2014 @ 9:00 AM
    5/15/2014 @ 2:00 PM
    5/2/2014 @ 1:30 pm
    5/2/2014 @ 2:00 PM
    5/2/2014 @ 2:15 PM
    5/2/2014 @ 8:45 AM
    5/2/2014 @ 9:00 AM
    5/2/2014 @1:00 PM
    5/2/2014@ 1:15 PM
    5/27/2014 @ 8:00 AM
    5/30/2014 @ 3:00 PM
    5/5/2014 @ 1:00 PM
    5/5/2014 @ 2:00 PM
    5/5/2014 @ 7:00 PM
    5/5/2014 @ 7:15 PM
    5/5/2014 @ 8:00 AM
    5/5/2014 @ 9:00 AM
    5/6/2014 @ 10:00 AM
    5/6/2014 @ 3:30 PM
    5/6/2014 @ 4:00 PM
    5/6/2014 @ 5:00 PM
    5/6/2014 @ 8:45 AM
    5/8/2014 @ 10:00 AM
    5/9/2014 @ 8:00 AM
    6/13/2014@3:00pm
    7/9/2014 @ 9:00 AM

    I want it to sort them in order month,day,year, time.

    TIA for any help.
    Reply With QuoteRegister To Reply
  2. 05-06-2014, 01:27 PM #2
    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: Dates in Excel Spreadsheet won't sort correctly.

    Try this formula in B2

    =IFERROR(VALUE(SUBSTITUTE(A2,"@","")),VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"PM"," PM"),"pm"," PM"),"@"," ")))

    Format Custom:

    m/d/yyyy h:mm AM/PM

    A
    B
    1
    2
    		 5/1/2014 2:15PM
    5/1/2014 2:15 PM
    3
    		 5/1/2014 @ 2:00 PM
    5/1/2014 2:00 PM
    4
    		 5/1/2014 @ 2:45 PM
    5/1/2014 2:45 PM
    5
    		 5/1/2014 @ 8:00 AM
    5/1/2014 8:00 AM
    6
    		 5/12/2014 @ 6:00 PM
    5/12/2014 6:00 PM
    7
    		 5/12/2014 @ 6:15 PM
    5/12/2014 6:15 PM
    8
    		 5/12/2014 @ 9:00 AM
    5/12/2014 9:00 AM
    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
  3. 05-06-2014, 01:29 PM #3
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Dates in Excel Spreadsheet won't sort correctly.

    Your data is more of a mess than you might think.
    With your sample data in A1:A32

    This regular formula, copied down and formatted as date/time, converts those values to proper Excel date/times
    Please Login or Register  to view this content.
    Sort by that "helper column"
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules
    Reply With QuoteRegister To Reply
  4. 05-06-2014, 01:34 PM #4
    canoeron
    canoeron is offline
    Registered User
    Join Date
    01-10-2014
    Location
    United States
    MS-Off Ver
    Excel 2016
    Posts
    18

    Re: Dates in Excel Spreadsheet won't sort correctly.

    The values are text formatted. You need to isolate out month, day, year & time and then change time to a timevalue. Finally concatenate them all together to get the sort order. Good luck!

    See attached for small example.TextDatesToValuesToSort.xlsx
    Reply With QuoteRegister To Reply
  5. 05-06-2014, 01:45 PM #5
    rbdiesel
    rbdiesel is offline
    Registered User
    Join Date
    05-06-2014
    Location
    mitchellville
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Dates in Excel Spreadsheet won't sort correctly.

    There is a lot more to the spreadsheet than just time and dates. so i can not add a cell... and i am apparently not as adequate on excel as once believed.
    Reply With QuoteRegister To Reply
  6. 05-06-2014, 01:49 PM #6
    rbdiesel
    rbdiesel is offline
    Registered User
    Join Date
    05-06-2014
    Location
    mitchellville
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Dates in Excel Spreadsheet won't sort correctly.

    I&I PROJECT TRACKING SPREADSHEET.xlsxI&I PROJECT TRACKING SPREADSHEET.xlsxThere is a lot more to the spreadsheet than just time and dates. so i can not add a cell... and i am apparently not as adequate on excel as once believe
    Reply With QuoteRegister To Reply
  7. 05-06-2014, 01:56 PM #7
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Dates in Excel Spreadsheet won't sort correctly.

    Using your posted workbook...
    Select the column of "dates"
    Replace @ with a space
    Replace AM with " AM" <without the quotes
    Replace PM with " PM" <without the quotes

    Now that the values are converted to Excel date/time values, you can sort them
    Reply With QuoteRegister To Reply
  8. 05-06-2014, 01:58 PM #8
    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: Dates in Excel Spreadsheet won't sort correctly.

    Please see attached your workbook with dates corrected.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 05-06-2014, 02:03 PM #9
    rbdiesel
    rbdiesel is offline
    Registered User
    Join Date
    05-06-2014
    Location
    mitchellville
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Dates in Excel Spreadsheet won't sort correctly.

    YOU'RE A LIFE SAVER!!!!! Thank you so much Ron!! So for future reference.... AM and PM is to be capitalized and a space after the last digit in the time...and NO @. Correct
    Reply With QuoteRegister To Reply
  10. 05-06-2014, 02:06 PM #10
    Ron Coderre
    Ron Coderre is offline
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Dates in Excel Spreadsheet won't sort correctly.

    Glad you got something you can use!
    Be sure to mark this thread as SOLVED (from the Thread Tools menu)

    BTW...Caps don't matter in the AM/PM section. But, Date/time values can't have extraneous characters in them. Also, if there are extra spaces, Excel will parse them out for you. But, if they're missing...the value remains as a string...not a date.
    Last edited by Ron Coderre; 05-06-2014 at 02:08 PM.
    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. [SOLVED] Dates seem formatted correctly, but will not sort newest to oldest.
    By MelindaCapri in forum Excel General
    Replies: 6
    Last Post: 02-11-2015, 02:34 PM
  2. Dates won't sort correctly
    By efaaonson in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-26-2012, 11:31 PM
  3. How to make slicer sort dates correctly?
    By Elias Hedberg in forum Excel General
    Replies: 1
    Last Post: 08-04-2011, 10:26 AM
  4. How to sort dates correctly?
    By Mark53 in forum Excel General
    Replies: 3
    Last Post: 01-10-2011, 07:35 PM
  5. My dates won't sort correctly?
    By kdietze in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-28-2007, 04:22 PM

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