Pivot not grouping dates correctly in rows

Hi there,

I have a data set of office entries and would like to create a chart to see how the number of people attending the office (by location) is changing month on month so I figured I'd need to arrange my data into a pivot table first and then plot into a line graph.

My data set has lines that are essentially every time a person swipes their card into an office location, however, I've adding a column called 'Entry' to mark a 1 where the person enters on the day so that we're not counting for when people are swiping multiple times in a day. I then want to pivot my data so that I have the locations across the top, the month as rows, and the number of entries as the values.

I initially pulled my month column into the rows and excel auto grouped my month, quarter, and year, and so I ungrouped and now excel is showing multiple lines for the same month, however if I try to group by month, then excel ignores the year and groups months in different years together in the same month. I would like to see unique month-year/year-month in the rows but I can't seem to do it.

I've searched multiple threads online on a similar topic but I don't seem to come across anyone with the same issue.

Please could someone assist?

KJ123

Change the value in "Month" column to =TEXT([@Date],"yyyy-mmm")

Hey Josephteh, thanks for responding! Ah so this is how I had it set up initially but the reason why I changed it was because it then orders the months alphabetically and so I have to reorder each time I get new data. Is this the only way I can get round my problem?

Change the formula to: =EOMONTH([@Date],0), then sort the PivotTable A to Z.

This worked. Thank you!