+ Reply to Thread
Results 1 to 4 of 4

Pivot Table Filter Formatting Question

  1. #1
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Pivot Table Filter Formatting Question

    Hi, i have attached a sample with a simple example.

    in Pivot Tab, my filter is in B1 and i choose 01/08/2017. But at first its at "dd-mm-yyyy" format. I wan it to show as "mm'yy". Hence i format it to "mmm/yy". I thot everything is in order until i choose another date (example, 01-06-2017) it should show Jun'17 coz i already format it to "mmm'yy". HOwever, it show 01-06-2017 again! When i choose 01/08/2017. It show Aug'17. When i choose 01/06/2017 or 01/07/2017, it show "dd-mm-yyyy"

    How to solve this? Also, another one can tell me how to make the Drop down list appear as "mmm'yy" instead of DD-mm-yyyy??
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Pivot Table Filter Formatting Question

    hi there. you can choose to show All in B1 first before formatting to what you hope to see. it should work after that.

    as for showing the drop down list in mmm'yy, i don't think it's possible.

    i do feel that you should be grouping the dates and using slicers instead. it's much quicker if you have tons of different period and more intuitive.
    1. drag the Date to the ROWS area.
    2. right-click any one of the Date and Group
    3. Choose Month and Year. Click OK
    4. Drag the Month and Year field back to the FILTERS area.
    5. go to the INSERT tab -> Slicer.
    6. select Date and Year. you can now select any month and year you desire.

    Slicers are available from Excel 2010 onwards

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Filter Formatting Question

    you want mm'yy or mmm'yy ?
    the way is the same

    (or I misunderstood)

    pt-fieldformat.jpg

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Pivot Table Filter Formatting Question

    Welcome

+ 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. [SOLVED] Pivot Table Filter question
    By melrose0507 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 04-04-2016, 10:04 AM
  2. Mulitple Pivot Table Filter updates...Excell 2007 question.
    By kriordan13 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-09-2014, 01:12 PM
  3. Replies: 1
    Last Post: 03-08-2014, 02:02 AM
  4. Pivot Table question - formatting
    By c1steady in forum Excel General
    Replies: 6
    Last Post: 02-20-2012, 06:02 AM
  5. Pivot Table filter question
    By angelesta in forum Excel General
    Replies: 1
    Last Post: 08-30-2011, 03:05 PM
  6. Pivot Table formatting question
    By mattbauer in forum Excel General
    Replies: 1
    Last Post: 02-23-2010, 07:59 PM
  7. Pivot Table Formatting Question
    By marmur1 in forum Excel General
    Replies: 3
    Last Post: 04-11-2005, 08:06 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