0

I have a pivot table with certain columns based on dates. So the column labels are in dates(image below). Excel automatically formats these dates to "dd.mmm" but I would like to just see them as weekdays ("dddd"). I've tried changing the number format of the date field in the field settings.

Pivot table

0

Unfortunately I don't have an answer on how to change the date format when grouping. There is another solution though.

If you add columns in your data source with "Month" and "day" and then add these two to the columns section in your pivot above your date field, then you don't need to group them, and then you can select your own date format.

0

There are 2 ways to get your result.

  1. Add a new at your data source table with the formula: =TEXT([@DATE],"dddd") and then create the PivotTable with the new Column. enter image description here
  2. Add the data source to PowerPivot and change the date format to dddd with the meansure: =FORMAT(Table1[DATE],"dddd") then create the PivotTable with the new column. enter image description here
  • This does solve my problem with the labels, but I was also using a timeliner for the pivot table above, using the process you mentioned would format the dates to a text and It would show the sum of values from two Mondays(eg; Mar 12th and Mar 5th) which is not what I want. – Gangula Mar 13 '18 at 11:54
  • If you want to filter data based on day, change the format "dddd" to "dd". – Lee Mar 14 '18 at 1:24
  • I want to filter based on date, but I want the dates to be displayed as "Day name". Your method formats the date to text which is not what I want. If I use the format method you mentioned, excel won't recognize the dates anymore. – Gangula Mar 15 '18 at 10:50
0

I'm not sure if I missed this feature earlier or if its a newly added feature, but it looks like we can change the Number format of the Column Labels in a pivot Table.

You can follow the following Steps (Screen-shot below):

  1. Right Click on the Field in the Columns Section
  2. Click on Value Field Settings.
  3. Click "Number Format" to open the formatting window.
  4. Go to "Date" Category to chose from an existing Category
  5. or Go to "Custom" Category to customize the format like below screen-shot.
  6. Edit the custom Format in the "Type" field and preview the sample (a)

Steps to follow to edit the Number format of a pivot table Column Label:

Steps

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.