+ Reply to Thread
Results 1 to 14 of 14

Problem sorting the date column

  1. #1
    Registered User
    Join Date
    02-04-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Problem sorting the date column

    Only half my file will convert to UK date format. The other half persists in showing dates in US format, i.e. with the month first.
    I want 03/02/14. Also, when I try and sort the column, I don't get the option to sort oldest to newest........what have I done wrong ?

  2. #2
    Registered User
    Join Date
    12-16-2013
    Location
    London, England
    MS-Off Ver
    MSO 2010
    Posts
    52

    Re: Problem sorting the date column

    Try selecting all of the data in all of the columns/rows and then formatting the right clicking, selecting format cells and ensuring the 'date' option is set to UK and select the format you want, this should solve it.

  3. #3
    Registered User
    Join Date
    02-04-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Problem sorting the date column

    It's definitely set to UK, but half the column doesn't change.........

  4. #4
    Registered User
    Join Date
    12-16-2013
    Location
    London, England
    MS-Off Ver
    MSO 2010
    Posts
    52

    Re: Problem sorting the date column

    Can you upload an example?

  5. #5
    Registered User
    Join Date
    02-04-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Problem sorting the date column

    Also, when I try and sort it, it always used to ask whether I wanted it sorted oldest to newest or vice versa. However now I don't get that option. I feel the whole column has not been formatted as being a DATE column ????

  6. #6
    Registered User
    Join Date
    02-04-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Problem sorting the date column

    Here's an extract from my file.......
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-16-2013
    Location
    London, England
    MS-Off Ver
    MSO 2010
    Posts
    52

    Re: Problem sorting the date column

    Ok,

    You are currently using US imported dates, hence they are aligned right, not left like UK dates

    Insert this formula into a BLANK cell in an erroneous row

    =DATE(RIGHT(B1,4),LEFT(B1,2),MID(B1,4,2))

    Assuming your dates start in B1, then copy this formula all the way down, this will generate the dates into the correct format.

    Hope this helps.

    I have attached the formula in action in Column H, please note that the dates Not formatted will work if you change 2 into 02 for the day.

    Hope this helps.
    Attached Files Attached Files

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Problem sorting the date column

    Using your data, I selected column B and clicked on the Data tab, Text to columns. I then clicked next until getting to the dialogue that has the date option. I selected the format of the dates (month, day, year) then clicked Finish. All the dates were converted to the Uk format (that I use).

    So, sort all the non-conforming dates so that you can select them together and go through the above process. If you don't sort to get the non-conforming dates together, the dates that are in UK format will likely become messed up.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  9. #9
    Registered User
    Join Date
    02-04-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Problem sorting the date column

    Wow, that's really helpful, many thanks for your time, much appreciated. In the actual spreadsheet my dates are in col M - can I just change the B in the formula to M ?

  10. #10
    Registered User
    Join Date
    12-16-2013
    Location
    London, England
    MS-Off Ver
    MSO 2010
    Posts
    52

    Re: Problem sorting the date column

    Correct, just change the formula to the column the dates are in. So B1 to M1 etc.

  11. #11
    Registered User
    Join Date
    02-04-2014
    Location
    East Anglia
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Problem sorting the date column

    That seems like it should be really straightforward. Many thanks for your time, I'll give it a go.

  12. #12
    Registered User
    Join Date
    02-11-2014
    Location
    teh
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Problem sorting the date column

    Hi, I have problem with sorting data with their values in another column! I have ''word[1 ... 168]'' ... which will be sorted like ''word[1]'', ''word[10]'', ''word[101]'' in column A, but I want to plot the figure from ''word[1]'', ''word[2]'' to ''word[168]''! Their values are in column B. How can I sort them like ''word[1]'', ''word[2]'', ... to plot their values?

  13. #13
    Registered User
    Join Date
    12-16-2013
    Location
    London, England
    MS-Off Ver
    MSO 2010
    Posts
    52

    Re: Problem sorting the date column

    Can you please upload an example?

    M

  14. #14
    Registered User
    Join Date
    02-11-2014
    Location
    teh
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Problem sorting the date column

    [QUOTE=mwinter;3583665]Can you please upload an example?

    I want to plot from 1 till 168 in column A (x) with their values in column B (y)!
    Attached Files Attached Files

+ 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. [SOLVED] Sorting by date problem
    By Redder Lurtz in forum Excel General
    Replies: 3
    Last Post: 07-12-2012, 06:20 AM
  2. Date sorting Problem
    By sprinter1801 in forum Excel General
    Replies: 3
    Last Post: 09-06-2010, 11:06 AM
  3. Problem with sorting by date
    By jaywizz in forum Excel General
    Replies: 2
    Last Post: 10-07-2009, 11:27 AM
  4. 3 columns; date and two data, problem is sorting and calculating with respect to date
    By phosphorescence in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-29-2008, 04:21 PM
  5. [SOLVED] problem sorting a date column
    By Middletree in forum Excel General
    Replies: 2
    Last Post: 08-16-2006, 09:15 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1