+ Reply to Thread
Results 1 to 6 of 6

Extract month from date, have it display as 'Jan', and have excel read it as a month.

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    97

    Extract month from date, have it display as 'Jan', and have excel read it as a month.

    I have a date. 1/1/2019. I can reference the cell and extract the date using =month(A1). It'll show as a 1.

    Is there a way to show that as Jan, but also have excel read it as a month? So when I filter on the column, the months aren't in alphabetical order, they are instead in order of the calendar.

    Currently, everything I have tried excel wants to put it in alphabetical order.

    Thanks
    Last edited by kufta4; 01-25-2019 at 12:28 PM.

  2. #2
    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,093

    Re: Extract month from date, have it display as 'Jan', and have excel read it as a month.

    Don't extract it ... link to it, for example, =A1, and use a custom format: "mmm"
    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


  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Extract month from date, have it display as 'Jan', and have excel read it as a month.

    Maybe I am not reading enough into this but if you format the cells as "mmm," they will show the month, but when you select the cell, you'll see that the data is still carried as a date.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  4. #4
    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,093

    Re: Extract month from date, have it display as 'Jan', and have excel read it as a month.

    You're welcome. Thanks for the rep.



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  5. #5
    Registered User
    Join Date
    01-12-2011
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    97

    Re: Extract month from date, have it display as 'Jan', and have excel read it as a month.

    Quote Originally Posted by dflak View Post
    Maybe I am not reading enough into this but if you format the cells as "mmm," they will show the month, but when you select the cell, you'll see that the data is still carried as a date.
    Ok, if I do that (I don't have the file right this second) and I pivot the dates and put them in columns, will I get Jan through Dec, or will I get every date in the data? This is what I was trying to accomplish (just Jan-Dec).

  6. #6
    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,093

    Re: Extract month from date, have it display as 'Jan', and have excel read it as a month.

    You'd need to right click on a date in the Pivot Table and choose Group (by month/year)

+ 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. Replies: 4
    Last Post: 05-17-2016, 05:32 PM
  2. [SOLVED] Change date in excel formulas from month to month automatically
    By ANDREAAS in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-02-2013, 09:34 AM
  3. [SOLVED] Display the first date of the month when the month and year are selected
    By john dalton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2013, 11:59 AM
  4. [SOLVED] display cell to show only Month/year to do sumif calc if today()= any day of month
    By fireguy7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-12-2013, 06:55 PM
  5. [SOLVED] IF statement to read month format and place month name in next cell
    By david1987 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2012, 11:41 AM
  6. Display end of month date for previous month
    By erikwvb in forum Excel General
    Replies: 3
    Last Post: 06-20-2011, 01:22 PM
  7. [SOLVED] Excel month dropdown list to generate day of week and date for selected month
    By aaaaaaaa in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-17-2010, 02:45 PM

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