exceltoxl.com

Hi There,

Today, when I was taking a class on PIVOT, one of my student asked this question and I found it challenging, not exactly but found two different methods to achieve this. So, thought of sharing with you. The Pivot Challenge is as below:

"How to Group Dates from Monday to Sunday using Pivot Table?"

Initially, I felt like pivot class is almost getting over and why is he asking grouping again (I covered group by words and other concepts yesterday). Any how, I should give him an answer. So, I took an example workbook where the starting day of the dump is from Thursday (08/02/2007) and the requirement to group the dates are opened cases on or after 08/25/2014. (08/25/2014 is Monday).

...

FILTER BY "IS AFTER":

I used Date Filters in the RowLabels to do CUSTOM FILTER which has "is after" 08/24/2014". Here, we should know one thing is pivot will consider the grouping based on the dump start date. As the start day in the dump is Thursday, my data is grouping from "08/21/2014 - 08/27/2014" as first week's data which is like Thursday to Wednesday where as the data which I required to group is from "08/25/2014 - 08/31/2014". Here is the challenge for us now.

How to do this? Pivot is not supporting my requirement!

Here is where we should use custom/manual grouping rather than what pivot grouping does for us automatically. To know how to use click on Custom/Manual grouping. 

Yes, this is how we can do it.

SECOND INSTANCE TO ACHIEVE THIS CHALLENGE:

I have second instance which is very easy that is, Just create a dummy dates backwards until you get start day as Monday. In my case, I created three dummy rows which has only cases opened date as  

Row 1: 08/01/2007

Row 2: 07/31/2007

Row 3: 07/30/2007

Now, refresh your pivot and see the dates will be grouped according to your requirement. WoW!

One more great tip right?

That's it Magic works.

If you are unaware of how to achieve the first result? please ping me using below comments section and I will help you out. Thanks for your time. Keep Excelling "exceltoxl.com"

DO YOU PIVOT?

Please share your experience about pivot tables and let us learn from your experience. If you find this example benefited for you, then use our social networking icons to share it on your timelines and help your friends, colleagues or co-hearts to learn Excel with your help.

If you want to support me for maintaining this blog, REGISTER here. It's absolutely free.

SOME IMPORTANT LINKS:

My Pivot Week

Mouse Right Click Tip

Few More Quick Tips

Thanks,