+ Reply to Thread
Results 1 to 12 of 12

1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

  1. #1
    Registered User
    Join Date
    07-24-2013
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    62

    1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    I need to create a new variable date with only the month and year.
    1/1/2016 5:30:00 AM to Jan-16

    This is answer in the previous post
    =TEXT(A1,"mmmm - yy")

    http://www.excelforum.com/excel-form...from-date.html

    But when I try to sort my dates oldest to newest it does not work since Excel is not treating my date variable as a date.

    Thank you in advance!
    Marvin
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    instead of sorting column B try to sort Column A

    or use formula as
    =TEXT(A2,"mmmm - yy")+0 and sort on column B

    Or in B1 type = A1 and then go to formatting select Custom and set formatting to MMMM - YY and sort on column B
    Last edited by hemesh; 01-27-2016 at 06:56 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    If you just sort on column A, you will get the data in DATE order which, by default, will sort the data into month and year order.

    Then, as has been suggested by hemesh, you can have a second column with the date formatted as you wish.

    See the updated sample workbook.

    Regards, TMS
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    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


  4. #4
    Registered User
    Join Date
    07-24-2013
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    62

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    I am sorry I am a little lost. just need the the month and year. an then sort it (date). I am creating a pivot table but it is not working. CanI create just a new date variable using my old (long date variable) and the sort my new date?

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    Can I create just a new date variable using my old (long date variable) and the sort my new date?
    No, not really, not using TEXT. TEXT creates a text string which, in this case, contains a month name and a couple of digits for the year. You will not be able to sort that column into anything like a date order as it will sort alphabetically.

    The example uploaded simply refers to your original long date but formats it using a Custom Format to show the data (date) as just a month and year.

    Regards, TMS

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    If posting code please use code tags, see here.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,434

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    I'm not sure what's wrong with his title:
    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)
    He has an existing long date and time and wants to "extract" the month and year and sort it ... and he has a problem sorting it. Seems to describe the problem, and there's no hint of a solution.

    I understand all that and have made a suggestion, which, admittedly, the OP seems to be struggling with. And I can't explain it any differently or offer any other alternative approach so I'll be dropping out.

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    I give up.

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

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)


  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    No idea what that means.

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

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    No words. Sorry if I have offended you.

  12. #12
    Registered User
    Join Date
    07-24-2013
    Location
    new york
    MS-Off Ver
    Excel 2003
    Posts
    62

    Re: 1/1/2016 5:30:00 AM to Jan 16 and then Sort (problem with sorting new date)

    So I guess there is not a solution for sorting my new date variable. It is ok! Thanks for trying!

    Marvin

+ 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. Excel 2016 Problem
    By lgchandana in forum Excel General
    Replies: 4
    Last Post: 12-16-2015, 06:07 AM
  2. Replies: 7
    Last Post: 05-09-2013, 05:15 AM
  3. [SOLVED] Sorting by date problem
    By Redder Lurtz in forum Excel General
    Replies: 3
    Last Post: 07-12-2012, 06:20 AM
  4. Date sorting Problem
    By sprinter1801 in forum Excel General
    Replies: 3
    Last Post: 09-06-2010, 11:06 AM
  5. 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
  6. Problem sorting Date
    By maxnca in forum Excel General
    Replies: 2
    Last Post: 05-31-2007, 03:49 PM
  7. [SOLVED] problem with date sorting
    By Middletree in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2006, 09:20 AM

Tags for this Thread

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