1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel: Dates: Month

Discussion in 'Business Applications' started by etaf, Jan 11, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,180
    First Name:
    Wayne
    I have a strange problem I hope someone can shed some light on

    I want to make a new column of months from a date
    I have a detached with column A as a normal date format 01/12/2006 DD/MMM/YYYY
    I then in column B use the month function - so = month(A1)
    it displays 12 - and if I go onto the Fx to see the expression helper / wizard
    I get told that i get a number returned 1-12 here 1 = Jan and 12 =dec

    so that i format the =month(a1) into a MMM-YY format and
    get Jan-1900 - I'm cool with the 1900 as i have not specified YYYY but why do i get Jan returned instead of Dec

    But what I'm after and maybe a simpler way is to have a dropdown on my pivot table of MM-YYYY from my date field rather than DD-MMM-YYYY

    so I can choose and show month data

    I'm sure I did this in the past just by using format - MMM-YYYY on a date field and it worked in the picot table OK

    any help appreciated
     
  2. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    If you want to display the text format of the month use this expression, where A2 is your field with the full date.

    =TEXT(A2,"MMM") Abreviated month

    =TEXT(A2,"MMMM") Full month text

    when you use the Month expression you need to change the format of the cell to a number, as this function only returns the number value of the month.

    FYI you can also exchange the M's with D's to get the day's in text
     
  3. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,180
    First Name:
    Wayne
    Thanks

    How would that display in the drop down list - I assume alpha rather than date order ?
     
  4. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    use the numeric as your sort and include the text as the identifier, you could create a custom sort with the text.
     
  5. etaf

    etaf Moderator Thread Starter

    Joined:
    Oct 2, 2003
    Messages:
    65,180
    First Name:
    Wayne
    sorry Ziggy1 - not sure how i would go about that
     
  6. Ziggy1

    Ziggy1

    Joined:
    Jun 17, 2002
    Messages:
    2,551
    sort using the numeric month, and then use your text in the Pivot, you can also use custom click the OPtions button in the sort menu and you can choose the text Months to sort by that column (highlight the whole range or you will mix the data).
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/534341

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice