Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am starting an Excel database that I want to use to merge with Word. I think Excel is better as a database than a table in Word would be because I have an awful lot of info. I also want to be able to sort by a particular column, in this case DOB.

    However, when I tried to sort by the DOB, as you can see from the example, it does not sort all the 01, 02, together, but it does whatever it wants. I probably am missing something.

    The DOB column is formatted by Date, Type 03/14/2001. The reason I want to sort by DOB is to see if there is someone born on that month and send them a birthday card.

    When I sorted by the DOB column:

    Code:
    This Is That I Get  This Is What I Want
    10/30/1923		  1/19/1941
    1/19/1941		   2/12/1948
    6/18/1947		   4/24/1996
    2/12/1948		   6/18/1947
    4/24/1996		   10/30/1923
    I went back, selected the column to be text, instead of date 03/14/2001, and manually typed 01, 02, etc., but got the same result.

    What am I not doing?

    Thank you so much in advance for any help you can give me.

    I am sorry that the columns are not working. I tried to press CTRL+TAB, SHIFT TAB, spaces, but was unable to make two columns here. I see two columns when I am typing but, not when I previewed it, but need some help. There are two columns, honestly, when I typed the text.

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you are referring to the left most column in your example, your sorting is occurring correctly. Don't forget that a date will sort chronologically, not by the month number designation. 1923 comes before 1946 which comes before 1947 etc. You would have to add another column for sorting on the month. Enter the formula =Month(A2) (copy down as necessary) sort on the new column.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By default, multiple spaces and tabs are ignored on web pages. To display spacing as entered, insert a [code] tag before, and a [/code] tag after the text.

    Excel will sort dates as complete dates, i.e. a date in 1923 is earlier than a date in 1941 or 1947 even if it falls earlier within the year.
    If you want to sort by day in the year, enter a formula of the form

    =100*MONTH(A2)+DAY(A2)

    in a column next to the data, and sort on that column.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I thought I might have to create some type of separate column, but wasn't sure. However, I want to sort not by the year but by the month. What I want is for all the ones (Jan) to be together, all the twos (Feb), all the threes (March), etc. Can somebody tell me how to do it.

    Again, thank you in advance for any response.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quoting myself:

    "Enter the formula =Month(A2) (copy down as necessary) sort on the new column."

    where A2 is the column that contains the date field

  6. #6
    2 Star Lounger
    Join Date
    Jun 2001
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you. Like I always say, "You guys are awesome."

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    =TEXT(A1,"m")

    Regards
    Bosco

  8. #8
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='bosco_yip' post='788827' date='13-Aug-2009 13:06']=TEXT(A1,"m")

    Regards
    Bosco[/quote]

    To use the TEXT() function and still have the months sort in the correct order, the formula would need to be:
    =TEXT(A1,"mm")

    Since the result of the TEXT() function is a string result, the formula in its current form would cause the months to sort as 1, 10, 11, 12, 2, 3, 4, etc OR Jan, Oct, Nov, Dec, Feb, Mar, Apr, etc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •