SOLVED
Home

Pivot Table - Date - Group by Month does not work

%3CLINGO-SUB%20id%3D%22lingo-sub-539975%22%20slang%3D%22en-US%22%3EPivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-539975%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3EI%20am%20working%20on%20my%20departments%20budget%2C%20and%20wish%20to%20show%20the%20costs%20per%20month.%20However%2C%20invoices%20are%20registered%20across%20the%20month%20(on%2002%2F01%2F2019%2C%2005%2F01%2F2019%2C%2013%2F01%2F2019%2C%20...).%20Now%2C%20the%20aim%20is%20to%20show%20per%20month%20the%20costs%20and%20not%20per%20date%20it%20is%20registered.%3C%2FP%3E%3CP%3E-%20I%20triple%20checked%20that%20the%20date%20field%20is%20indeed%20a%20date%20(I%20can%20change%20the%20date%20to%20any%20date%20format%20I'd%20like)%3C%2FP%3E%3CP%3E-%20I%20even%20can%20use%20a%20'date'%20timeline%3C%2FP%3E%3CP%3EHowever%2C%20when%20I%20click%20on%20group%20like%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fgroup-or-ungroup-data-in-a-pivottable-c9d1ddd0-6580-47d1-82bc-c84a5a340725%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fgroup-or-ungroup-data-in-a-pivottable-c9d1ddd0-6580-47d1-82bc-c84a5a340725%3C%2FA%3E%3C%2FP%3E%3CP%3Eeither%3C%2FP%3E%3CP%3E-%20When%20I%20select%20one%20date%2C%20I%20see%20%22cannot%20group%22%3C%2FP%3E%3CP%3E-%20when%20I%20select%20all%20dates%2C%20it%20just%20group%20the%20dates%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20I%20restarted%20Excel%3C%2FP%3E%3CP%3E*%20I%20verified%20that%20the%20group%20is%20date%3C%2FP%3E%3CP%3E*%20I%20updated%20to%20the%20latest%20version%20of%20excel%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-539975%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540130%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540130%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F336567%22%20target%3D%22_blank%22%3E%40SBiebaut86%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%20you%20have%20your%20pivot%20set%20like%20this%20and%20then%20right%20click%20on%20date%20and%20you%20don't%20get%20the%20Group%20Option%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20417px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112428i28715F30EC8E1E88%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540244%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540244%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3BI%20get%20the%20group%20option%2C%20but%20once%20I%20click%20it%2C%20it%20tells%20me%20'cannot%20group%20that%20selection'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-540324%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-540324%22%20slang%3D%22en-US%22%3EAvoid%20having%20anything%20else%20in%20your%20row%20labels%20box%20except%20date%20and%20then%20try%20grouping%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-542542%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-542542%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F336567%22%20target%3D%22_blank%22%3E%40SBiebaut86%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fcontexturesblog.com%2Farchives%2F2018%2F01%2F18%2Fproblem-grouping-pivot-table-items%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESee%20if%20this%20helps.%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545491%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545491%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%3BI%20was%20trying%20to%20apply%20your%20solution%2C%20when%20suddenly%20the%20pivot%20table%20itself%20divided%20my%20date%20into%20months%3A%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20253px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112547i30DC76B8E4A43828%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Untitled2.jpg%22%20title%3D%22Untitled2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%2C%20I%20have%20no%20idea%20how%20I%20did%20this.%20the%20original%20table%20only%20has%20'Date'%20(not%20months).%20He%20added%20the%20field%20'month'%20himself.%20It%20is%20perfect%2C%20because%20this%20is%20exactly%20what%20I%20need.%20(with%20this%2C%20I%20don't%20need%20to%20group).%20However%2C%20how%20did%20I%20do%20this%3F%20how%20do%20I%20replicate%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545505%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545505%22%20slang%3D%22en-US%22%3EIf%20you%20right%20click%20on%20the%20months%20in%20the%20column%20of%20your%20pivot%20table%20is%20Ungroup%20and%20option%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545538%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545538%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3Btrue%20true%2C%20I%20can%20ungroup%20it.%20--%20and%20I%20can%20group%20it.%20Sweet.%20How%20does%20that%20make%20sense%3F%3C%2FP%3E%3CP%3Ewell%2C%20no%20problem%2C%20it%20works%20%3A)%20(could%20it%20be%20that%20it%20is%20because%20I%20changed%20the%20format%20of%20the%20date%20column%3F%20first%20it%20was%20dd-mm-yyyy%20and%20then%20I%20changed%20it%20to%20dd.mm.yyyy%20(to%20see%20if%20everything%20would%20change%2C%20and%20everything%20indeed%20did)).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eoh%20well%2C%20thank%20you%20so%20much%20for%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545565%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545565%22%20slang%3D%22en-US%22%3E%3CP%3EGreat%2C%26nbsp%3B%20so%20are%20you%20using%20the%20Power%20Pivot%20data%20model%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-545854%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Date%20-%20Group%20by%20Month%20does%20not%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-545854%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3BI%20have%20no%20idea%20what%20the%20Power%20Pivot%20data%20model%20is%2C%20but%20I'm%20googling%20it%20right%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E
SBiebaut86
Occasional Contributor

Dear all,

I am working on my departments budget, and wish to show the costs per month. However, invoices are registered across the month (on 02/01/2019, 05/01/2019, 13/01/2019, ...). Now, the aim is to show per month the costs and not per date it is registered.

- I triple checked that the date field is indeed a date (I can change the date to any date format I'd like)

- I even can use a 'date' timeline

However, when I click on group like here: https://support.office.com/en-us/article/group-or-ungroup-data-in-a-pivottable-c9d1ddd0-6580-47d1-82...

either

- When I select one date, I see "cannot group"

- when I select all dates, it just group the dates

 

* I restarted Excel

* I verified that the group is date

* I updated to the latest version of excel

9 Replies

@SBiebaut86 

 

So you have your pivot set like this and then right click on date and you don't get the Group Option?

 

image.png

@Wyn Hopkins I get the group option, but once I click it, it tells me 'cannot group that selection'

 

 

Avoid having anything else in your row labels box except date and then try grouping

@Detlef Lewin I was trying to apply your solution, when suddenly the pivot table itself divided my date into months: Untitled2.jpg

 

The problem is, I have no idea how I did this. the original table only has 'Date' (not months). He added the field 'month' himself. It is perfect, because this is exactly what I need. (with this, I don't need to group). However, how did I do this? how do I replicate this?

If you right click on the months in the column of your pivot table is Ungroup and option?

@Wyn Hopkins true true, I can ungroup it. -- and I can group it. Sweet. How does that make sense?

well, no problem, it works :) (could it be that it is because I changed the format of the date column? first it was dd-mm-yyyy and then I changed it to dd.mm.yyyy (to see if everything would change, and everything indeed did)).

 

oh well, thank you so much for the help.

 

Great,  so are you using the Power Pivot data model?

@Wyn Hopkins I have no idea what the Power Pivot data model is, but I'm googling it right now.

Related Conversations