Filter column labels in pivot by days using VB.NET

Question

I have my pivot field code for submitted date.

ptField2 = ptTable2.PivotFields("Submitted Date")
    With ptField2
        .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
        .Name = "Submitted Date"
        .Caption = dtIntFrom.Value.ToString("MM-dd-yyyy")
        .ClearAllFilters()
        .PivotFilters.Add(Type:=Excel.XlPivotFilterType.xlCaptionEquals, DataField:="Submitted Date", Value1:=date_stamp)
    End With

Here is my target date: mar15-mar31, 2016

  date_stamp = Format(dtIntFrom.Value(), "m/d/yyyy") '3/15/2016
  date_stamp2 = Format(dtIntTo.Value(), "m/d/yyyy") '3/31/2016

When I run my program, it displays all dates from my target date.

I want to filter my selected target date per week and it must look like this:

Thanks for any help!

Answer 1

After a couple of days, I already found the answer using the Range.Group Method

Also another mistake is I code .xlColumnField first before the .xlDataField . It should be on the last.

Sample below:

ptField2 = ptTable2.PivotFields("Total Pages")
With ptField2
     .Orientation = Excel.XlPivotFieldOrientation.xlDataField
     .Function = Excel.XlConsolidationFunction.xlSum
     .Name = "Pages"
     .NumberFormat = "#,##0"
End With

ptField2 = ptTable2.PivotFields("Group")
With ptField2
     .Orientation = Excel.XlPivotFieldOrientation.xlRowField
     .Name = "Group"
End With

ptField2 = ptTable2.PivotFields("Submitted Date")
With ptField2
     .Orientation = Excel.XlPivotFieldOrientation.xlColumnField
     .Name = "Submitted Date"
     .DataRange.Cells(1).Group(True, True, 7, New Boolean() {False, False, False, True, False, False, False})
End With