I must admit, my Excel use has declined significantly since returning to school full-time. But, I still find plenty of uses – and, because they are not my routine office tasks, sometimes they are more interesting and I have more to write about.

This one comes from a project I am helping a family member on: some basic financial analysis and reporting. Given his transactions he would like them reported in months and weeks.

Remembering Excel 2016’s improved functionalities in grouping PivotTable records by date, I wasn’t expecting much of a fight. Turns out it was a little trickier than I thought.

Download the exercise file here….

Plan A: PivotTable Custom Grouping

We’ve discussed this before, it’s fairly straightforward. I create a custom grouping of dates, 7 days at a time. Turns out 1/2/2017 is a Monday, so no need to stagger the dates – although that is possible with the Start By option.

Right-clicking on the PivotTable, I head down to Group By and select Days. (I must admit that the clicking is rather counter-intuitive here – if you have any other fields checked on, you need to click on those first to check them off.)

But What About My Months?

So we have our data grouped by week. How about Month and Week?

Correct me if I am wrong, cyberspace – but it appears that using this option, I can only see my data grouped by week. Lame!

Fortunately in Excel there’s almost always a workaround. So I thought about it, and wondered if maybe the WEEKNUM() function might be one such workaround.

First, I added a column to my table. Here’s a great example of why tables are the way to go – I add to one cell and it fills down the table automatically. The PivotTable will also automatically include this field upon refresh. Nice!

After re-grouping my PivotTable to Months once more, I refresh my PivotTable. Let’s see how this works!

Pretty good. This time, we have an additional field, WeekNum, that complements the Date field. Of course, some week numbers are spread across two rows, because they occur in two different months. Now we are running against the laws of time! So I’ll stop here.

I am surprised I never had to do this before in Excel, and a little disappointed that 2016 didn’t make this a little easier. Like I said, though, there is fortunately a workaround to nearly anything you need to do in Excel – that’s where the magic happens!

More Pivot where that came from…

PivotTables are probably my favorite tool in Excel – I’ve written about them a lot. Check out my free Guide to PivotTables to get started.

Better yet, subscribe to my newsletter and get your free ebook, “The Beginner’s Guide to Getting Hired with Excel.” Continue getting content for awesome analysts.