Grouping Dates in Pivot Tables

Great post as usual. This feature works well in Excel 2007. I largely avoid using it in 2003 because it will only let you group in this way if all entries in that field are a date – and in particular that means no blanks. This stops you selecting whole columns for the data source.

I work around this by using the year(), month() and day() functions to give new columns of data that strip out the year month and day from the date and then use these columns as page, row or column fields.

If you are still baffled by all of this pivot table talk, take a look at my free Pivot Table Training Video at (2003 and 2007):

http://www.pivot-tables.biz/FreeVideo.htm

Chandoo:

this is exactly the reason why I suggested to change the layout on the data table for the Touch challenge.

In the way you upload it, you are not able to work with the dates, so you can group it as you mention on this post.

Also, I’ve found that sometimes, if you have blanks or non-date data in a field (even though it looks like data), you are not able to use this feature. to do so, the autofilter on that field comes handy to rapidly detect those non-date inputs. Another trick is to select the entire field and clear its format, so you can easily find those bugs.

Regards,

Martín.

Now this is a trick I didn’t know about. Well done! Not quite as slick as a tool like Tableau, but pretty darn useful.

I get fouled up sometimes by extending the range to include a blank past the last row of data.

Martin/Dale
As far as I can see the problem of grouping dates with blanks no longer exists in 2007. I have just tried it when I read this post.

This problem stopped me using date groupings in 2003 (I have been using year(), month(), etc. to strip the data out of the date instead).

I’ve used that trick in the past, but the blank row thing is a bummer.
I like to be able to import new data and just refresh the pivot table. Even filtering out blank rows doesn’t allow grouping. I know I could write a macro to create the pivot table every time, but it’s a hassle.
Another option is to add a column and use the MONTH, WEEKDAY or HOUR formulas, if that is how you plan to group the data.

Sorry–just read Glen’s post. I’m still using 2003. If it allows you to group with blank rows in 2007, that might be enough to get me to upgrade.

This is a great post. I recently started using Pivot table for data analysis and is working well. My frustration is with some of the easy stuff – say, i have a list of fruits

apple – 1
pear – 1
apple – 1

How many instances of apple, pear do I have. Maybe it is my limited knowledge of excel (which is getting better thanks to this blog) but I don’t see an easy way out.

Any help will be much appreciated.

Hi Chandoo,

May I say first thanks, this is a great website and a great resource.
I’ve recently had to group by Year/Qtr using 2003 and refered to this at Datapig http://www.datapigtechnologies.com/flashfiles/pivot5.html
But now I’ve got 2007 I’m glad you did this new post here

One frustration with Excel’s date grouping is that Quarters are for calendar years only. In my world Quarter 1 starts in July.

Here’s a bit of code to add to the ThisWorkbook module. This works by changing the captions of quarterly groups from ‘Qtr 1′ to ‘Q3′. It runs after the pivot table update event, because updating the pivot table will change quarter captions back to the defaults.

‘=================================================
Option Explicit
‘ IsUpdated is a global variable that lets us know
‘ if the Pivot Table Update event has been triggered by the user
‘ or by our code in this module
Private IsUpdated As Boolean

Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target
As PivotTable)
If Not IsUpdated Then
If SetFiscalQtr(Target) Then
Application.StatusBar = “Fiscal Year Quarters Set”
IsUpdated = False
End If
End If
End Sub

Private Function SetFiscalQtr(pt As PivotTable) As Boolean
‘ Function to set Quarters From Excel default to fiscal year
‘ i.e. Q1 = Jul, Aug, Sep etc.
If FieldExists(pt, “Quarters”) Then
IsUpdated = True
With pt.PivotFields(”Quarters”)
‘ could check to see if we have already
‘ renamed the quarters, or just error and keep
‘ going
On Error Resume Next
.PivotItems(”Qtr3″).Caption = “Q1″
.PivotItems(”Qtr4″).Caption = “Q2″
.PivotItems(”Qtr1″).Caption = “Q3″
.PivotItems(”Qtr2″).Caption = “Q4″
.AutoSort xlAscending, “Quarters”
‘ turn error checking back on
On Error GoTo 0
End With
SetFiscalQtr = True
Else
SetFiscalQtr = False
End If
End Function

Private Function FieldExists(pt As PivotTable, strField As String) As
Boolean
‘ checks if strField is a Pivot Field in the Pivot Table pt
‘ true if field exists
Dim fld As PivotField

FieldExists = False
For Each fld In pt.PivotFields
If fld.Name = strField Then
FieldExists = True
Exit Function
End If
Next
End Function

Cheers,

Ed

Is there a way to group on non-date values? For example, I have expenses from various companies that I need to compare. I have normalized the data, and then I pivot – so that the categories and line items are the rows, and I have a column for each company. However, it’s a terrible waste of real-estate – the categories take up too much room. It would be great if they could be grouped….

Suggestions?

hi chandoo,

this post is just what i need to feedback a bug/problem in PivotTable (Excel 2007).

I’ve created a new sheet for pivottable with date grouped into month/year.

BUT, i needed to create a new pivottable sheet (from the same source table) with NO date grouping. However, this new pivottable will not show the full, ungrouped dates. It stumbled me for some time until i figured out that the 1st pivottable has date grouping!

is there a solution to this?

thanks!

thanks chandoo!

am glad it’s not a weird problem that i’m facing

Hi. I am trying to chart expenses per month that range from Sept 08 to October 09. When I group by month in the pivot table it only gives 12 months (Jan- Dec) and groups the Sept 08 and Sept 09 data together. Is there a way to group by month (Sept-08 through Oct-09) that will allow me to use greater than 12 months?

How do you do this with a fiscal year, say from December to November? It seems to only allow for calendar years.

I need it for 3 years, not just one single year.