Skip to content

Excel VBA – How to Group by Date Field in a PivotTable

    To group data in a PivotTable by a date field use LabelRange.Group.  LabelRange is a property of the PivotField object. Group is a method with the following parameters.

    LabelRange .Group(Start, End, By, Periods)
    NameRequired/OptionalData TypeDescription
    StartOptionalVariantIf omitted or  True , the first value in the field is used.
    EndOptionalVariantIf omitted or  True , the last value in the field is used.
    ByOptionalVariantIf field is  numeric , this argument specifies the size of each group. If the field is a date, use to group by x days.
    PeriodsOptionalVariantNot relevant to numeric fields
    Periods:=Array(False, False, False,False, True, False, True)

    The code below relates to the data shown above.

    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim PTcache As PivotCache
    Dim PT As PivotTable
    Dim SLcache As SlicerCache
    Dim SL As Slicer
    Dim df As PivotField
    'Define the cache for the PivotTable
    Set PTcache = wb.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=Range("Sales_Data[#All]"),Version:=xlPivotTableVersion15)
    'Create the PivotTable
    Set PT = ws.PivotTables.Add(PivotCache:=PTcache, _
    TableDestination:=Range("J1"), TableName:="SalesAnalysis")
    PT.AddFields RowFields:="Date",ColumnFields:="Region"
    PT.AddDataField Field:=PT.PivotFields("Revenue"), _
    Caption:="No of Transactions", Function:=xlCount
    'Group Date Field by Month and Year
    PT.RowAxisLayout xlTabularRow
    Set df = PT.PivotFields("Date")
    df.LabelRange.Group _
    Start:=True, End:=True, _
    Periods:=Array(False, False, False, False, True, False, True)
    df.Caption = "Month"

    The code above would create the PivotTable shown below.