Forum Discussion

grace15's avatar
grace15
Copper Contributor
Nov 20, 2018

Date Formatting in pivot table

Date format in Pivot table is not working in filters or in columns/rows. (it has always worked so far, and in this particular case it's not)  If I go to the source data, it is a date format.     I...
grace.lee@purplebricks.com.au
ahmad ali's avatar
ahmad ali
Brass Contributor
Nov 20, 2018

grace15

 could you upload the file( you may delete some sensitive data)

there are many date formats

grace15's avatar
grace15
Copper Contributor
Nov 21, 2018

Please see attached, Thank you for looking into this. 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 21, 2018

     

    Creating Pivot Table add data to data model

    Open Power Pivot and add new column Published Months

    Add one more column SortMonths

    Select Published Months column, Sort by Columns and sort it by latest column

    Right click SortMonths column and hide it

    Return back to Excel sheet and add Published Months as a filter

    Result is in attached file, second PivotTable

  • ahmad ali's avatar
    ahmad ali
    Brass Contributor
    Nov 21, 2018

    Hello,

     

    one last thing

    the grouping looks fine ( I'm not sure if this is what you mean-the image below)

    • Willy Lau's avatar
      Willy Lau
      Iron Contributor
      Nov 21, 2018

      Actually, grace15 is asking why when the month group filter is set, but if you filter by day, then, you still need to uncheck the date out of the month.  She doesn't want this.

  • ahmad ali's avatar
    ahmad ali
    Brass Contributor
    Nov 21, 2018

    Hello Grace15,

     

    Please see the attached- the file has 2 sheets- each sheet is a different option for you

    option 1: in the Pivot Table fields ==> FILTERS ==> click on (published dates) ==>Field settings

    ==> number formats ==> Dates ==> Mar-12

    option 2: add a new column with the desired date format

     

    I hope this will be what you have been seeking

     

Resources