+ Reply to Thread
Results 1 to 12 of 12

How to sort Date column 56 when = to 55 and formatted to display only month name

  1. #1
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

    How to sort Date column 56 when = to 55 and formatted to display only month name

    My column 55 holds birthdates and formatted as 1-9-12.
    My column 56 equals column 55, and is formatted to only show month . . . January etc.

    I've tried sorting column 56 and it will not sort this column, as I think that it is caused by using date data from column 55.

    I don't want to use a macro if I don't have to.

    Any ideas?
    Last edited by Launchnet; 02-18-2012 at 10:13 PM.
    Thanks for helping . . .
    Matt @ Launchnet

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,984

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    It doesn't matter how you format it, it's still a date.

    Try: =TEXT(BC2,"mmmm") and copy down.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    When I enter your formula into column 56, I get #NAME?

    I don't think your formula will pickup the value from column 55, which it has to do, as I still need column 55 to exist.

    The solution may only be by running a macro whenever we need to use that column.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,984

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    Perhaps try changing the comma to a semicolon?

    Column BC is column 55.

    Regards

  5. #5
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    No . . . I get a formula error.

    Thanks for telling me what the BC was. I know better, but currently I am using numbeers rather than alpha and I just didn't think

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,984

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    Post the workbook. I can't keep guessing at what it might be.

  7. #7
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    Thanks . . . I've been gone again.

    Attached is workbook.

    I have explained on worksheet what I need.

    I only used 2 columns in my example, but I have many more in my database

    Thanks again

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,984

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    No workbook ...

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,984

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    If Mohammed will not go to the mountain, the mountain must come to Mohammed ...

    See attached example.

    Regards, TMS
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    Please see below attachment sample.

    I apologize for forgetting to attach my sample workbook. It certainly wasn't intentional. Again, I've been gone for a few days and couldn't reply sooner. At my age ( 80 ), every second is very valuable and you wouldn't believe all the activities that I am involved in. Most people think that I am in my early 60's at most.

    God is very good to me and I love him very much for all he does. I believe that I have to work for him in return.

    Thanks TM


    SortProblem.xls

  11. #11
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,984

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    Without seeing a real example of the data, I'm unsure of the significance of the blank rows.

    However, If you just want to see the rows with birthdays in a particular month, you could use Autofilter ... which is what I have used to select the month.

    Just select all the data from row 11 (the header row) down to the end of your data. Then choose Autofilter form the menu.

    If you're using Excel 2003, select Data | Filter | Autofilter.

    If you want to sort the data by month, you'd need to use a Custom Sort. Again, select all the data from (and including) row 11 to the end of the data. Now sort the data, indicating that you have a header row and choosing options. Click on the drop down box and choose the list of months. Click OK a couple of times to sort by month.

    That said, there's not much point sorting the data (and potentially getting it out of sequence) if you only want to see the rows with a specific month.

    See teh attached example.

    Regards, TMS
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    09-04-2007
    Location
    Ontario, Ca
    Posts
    624

    Re: How to sort Date column 56 when = to 55 and formatted to display only month name

    Well, I took your suggestions and ended up with the formula . . . =IF(BC12="","",TEXT(BC12,"mmmm"))

    I used my own sorting method and it works perfectly. I will mark as solved.

    Many thanks for the help and suggestions.

    I'll mark it solved.

+ 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