How to group dates in Pivot Table

Date grouping offers lots of flexibility which would satisfy your business needs.  Learn how to group dates by different levels of granularity (year, quarter, month, week, day, hour) without adding more functions in Pivot Table.

When to group dates?

All transactional data usually has a date/time column. In fact you may have multiple dates like order date, shipping date, delivery date and so on. Usually, time is also captured along with dates.

We often need to summarize daily data into weekly, monthly, quarterly and yearly reports. Typically most of us manually add more columns next to the original date column and use various type of date time functions to get what we want.  It is messy, frustrating and time consuming.

There is no need to do this manually.  Pivot Table provides you with an INSTANT solution for this purpose.  Watch this video to find out how in just 6 minutes:

https://www.youtube.com/watch?v=24jr3ZUbQS4

Customization Required

In business context, sometimes the months may not necessarily end on the last day of the month. Sales may continue for few more days and the monthly processing is finished by 2nd or 3rd of the month. There is no way to handle this in Pivot Table.

Similarly, you may have financial year that is different from the fiscal year, i.e. financial year that starts in April or July instead of January.  This would require a different approach for calculations of years and quarters.

Both these problems can be solved using VLOOKUP based additional column in the raw data.

Pivot Table – The Second Honeymoon.

This date grouping video is part of my next MOOC course on Pivot Table.  I am nearly half way through – already finished creating content for the first 27 lectures.   Pivot Table is a comprehensive topic and there are lots of  things that I want to cover, so the topic list keeps expanding. Stay tuned for further announcements.