+ Reply to Thread
Results 1 to 2 of 2

Sorting by date (different date formats)

  1. #1
    Registered User
    Join Date
    03-04-2010
    Location
    England
    MS-Off Ver
    Excel 2002
    Posts
    7

    Sorting by date (different date formats)

    Hi,

    Currently have a spreadsheet with a column that has dates in and the dates are in different formats E.G: 01.01.2010 & 1.01.2010 & 1.1.10 etc...

    I have a requirement to sort this column into order but the standard sort option is not correctly sorting prob due to the different formats.

    As I have over 6500 dates to sort its too much to go and change it all into the same format.

    How can I sort the dates?
    Last edited by mr_spaceuk; 03-18-2010 at 06:57 AM.

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Sorting by date (different date formats)

    You're probably looking at text, not dates. In order to sort them in a chronological fashion, you have to convert them to real dates first.

    Depending on your system settings, you may need to replace the . in 1.1.2010 with a / like 1/1/2010. You can use a find and replace to do that.

    Then use =Datevalue(A1) to convert the text in cell A1 into a date. Format the result in a date format of your choice and copy the formula down. Now you should have real dates in that column and you can sort by that column.

    If that does not help, post a sample workbook and specify what the default date format is for your computer settings.
    Last edited by teylyn; 03-18-2010 at 06:53 AM.

+ 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