Pivot Table Date Field

[mikeymay]

I have a set of data that is in a table format

The Month column shows dates from Jan 2012 to Oct 2024

I have a Pivot Table that is connected to this table and I want to use a filter for the Month to show the total by Client

But when I click on the filter to filter by Month, the options only show the month names rather than 1 January 2012, 1 February 2012, etc, etc, 1 October 2024.

I have no idea what fixes this as it makes no sense what Excel is doing......

Anyone else had this problem or know of a fix?


TIA

 

[Alex Blakenburg]

What happens if you
• right click on the word month
• select Field Settings
• click on Number Format button (bottom right corner)
• Custom Format > change it to mmm yyyy
does that fix it ?

Grouping is another option but I would have expected the word month to have changed so that is differentiates from the original Month heading.

 

[mikeymay]

What happens if you
• right click on the word month
• select Field Settings
• click on Number Format button (bottom right corner)
• Custom Format > change it to mmm yyyy
does that fix it ?

Grouping is another option but I would have expected the word month to have changed so that is differentiates from the original Month heading.

No Joy....still showing the month names with the < 01/01/12.

The Month column in the table is showing the month values in a dd/mm/yy form at and the cells are formatted to show as d mmmm yy so that column is being read as a date by Excel.

 

[mikeymay]

What happens if you
• right click on the word month
• select Field Settings
• click on Number Format button (bottom right corner)
• Custom Format > change it to mmm yyyy
does that fix it ?

Grouping is another option but I would have expected the word month to have changed so that is differentiates from the original Month heading.

I can only assume it has something to do with the Year column in the table as well.

I have had issues before when working with dates in PTs and as you said, the Group option can fix these but the PTs don't seem to work intuitively at times when working with dates.

The only solution I can think of is to drop the Year filed into the filter and select the appropriate year before selecting the month.

 

[RoryA]

It sounds like your date field has been grouped in the pivot table. Try ungrouping it.

 

[mikeymay]

It sounds like your date field has been grouped in the pivot table. Try ungrouping it.

This field isn't grouped or at least it doesn't look like it is

 

[mikeymay]

It sounds like your date field has been grouped in the pivot table. Try ungrouping it.

When the Month cell in the PT is selected, the Ungroup option within the PT ANalyse ribbon isn't available

 

[mikeymay]

Can either of you recreate this issue or are you able to see the months within the years rather than just the month names?

 

[RoryA]

You can't ungroup it while it's a filter field. Drag it to the row area and then try ungrouping it.

 

[mikeymay]

You can't ungroup it while it's a filter field. Drag it to the row area and then try ungrouping it.

So that has worked when it's added as a row

But when I add it back as a filter, I have the same problem.

This makes no sense really.....each cell in the table has a specific value and when that field is dropped into a filter, you'd expect it to allow you to filter on each unique value. When you do this with text you don't have this problem.....