SOLVED

Pivot table displays only month of full date - can't display year.

%3CLINGO-SUB%20id%3D%22lingo-sub-207857%22%20slang%3D%22en-US%22%3EPivot%20table%20displays%20only%20month%20of%20full%20date%20-%20can't%20display%20year.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207857%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%26nbsp%3B%20I%20have%20created%20a%20pivot%20table%20from%20a%20data%20set.%26nbsp%3B%20One%20of%20the%20fields%20in%20the%20data%20set%20is%20a%20date%20field%20(and%20I've%20checked%2C%20Excel%20is%20recognising%20it%20as%20a%20date%20in%20the%20format%20mm%2Fdd%2Fyyyy).%26nbsp%3B%20However%2C%20when%20I%20add%20this%20field%20to%20the%20rows%20of%20my%20pivot%20table%2C%20it%20only%20displays%20the%20month%20in%20text%20format%20(ie%2C%20%22Feb%22)%2C%20and%20then%20creates%20a%20separate%20column%20with%20the%20year.%26nbsp%3B%20I'm%20trying%20to%20get%20it%20to%20display%20the%20full%20date%20in%20the%20pivot%20table%20in%20a%20single%20column%2C%20not%20split.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-207857%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207877%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20displays%20only%20month%20of%20full%20date%20-%20can't%20display%20year.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207877%22%20slang%3D%22en-US%22%3ESo%20simple%2C%20thank%20you%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-207873%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20displays%20only%20month%20of%20full%20date%20-%20can't%20display%20year.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-207873%22%20slang%3D%22en-US%22%3E%3CP%3ECatherine%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eright-click%20on%20a%20month%20or%20date%20and%20select%20%22Ungroup%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2270309%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20displays%20only%20month%20of%20full%20date%20-%20can't%20display%20year.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270309%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EShould%20have%20looked%20here%20before%20I%20wasted%20an%20hour%20on%20this.%26nbsp%3B%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281221%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20displays%20only%20month%20of%20full%20date%20-%20can't%20display%20year.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281221%22%20slang%3D%22en-US%22%3EThanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2320100%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20displays%20only%20month%20of%20full%20date%20-%20can't%20display%20year.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2320100%22%20slang%3D%22en-US%22%3EThanks%20very%20much!%3C%2FLINGO-BODY%3E
New Contributor

Hi.  I have created a pivot table from a data set.  One of the fields in the data set is a date field (and I've checked, Excel is recognising it as a date in the format mm/dd/yyyy).  However, when I add this field to the rows of my pivot table, it only displays the month in text format (ie, "Feb"), and then creates a separate column with the year.  I'm trying to get it to display the full date in the pivot table in a single column, not split.

 

Please help?

5 Replies
best response confirmed by Catherine Klopper (New Contributor)
Solution

Catherine,

 

right-click on a month or date and select "Ungroup".

 

So simple, thank you so much!

@Detlef Lewin 

Should have looked here before I wasted an hour on this.  Thanks