Pivot Table - Date - Group by Month does not work

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:


- 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



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



@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 (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
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies