How to group time values by hour (not minute) in Excel PivotTable using VBA?

Question

I'm creating a PivotTable in Excel using VBA. One of the fields is a time value (formatted as "hh:mm:ss"), and I want the row labels to show each hour (e.g., 00:00, 01:00, 02:00), not every minute.

Here's the relevant part of my code:



With ptHour
        ' Filtros
        .PivotFields("Vehicle id").Orientation = xlPageField
        .PivotFields("Type").Orientation = xlPageField
        .PivotFields("Confirmation").Orientation = xlPageField
        
        .PivotFields("Fecha").Orientation = xlColumnField
        
        .PivotFields("hour").Orientation = xlRowField

        .AddDataField .PivotFields("Vehicle id"), "count of Vehicle ID", xlCount
        
        On Error Resume Next
       .PivotFields("hour").LabelRange.Group _
            Start:=TimeValue("00:00:00"), _
            End:=TimeValue("23:59:59"), _
            By:=TimeValue("01:00:00")
        On Error GoTo 0
End With

The source data in column K is formatted as "hh:mm:ss" using:

wsGuardian.Range("K6:K" & LastFila).NumberFormat = "hh:mm:ss"

However, the PivotTable still shows every minute (e.g., 08:01, 08:02, etc.) instead of grouping by hour.

Can you add a calculated column to your source data?

Frank Ball
– Frank Ball

2025-11-14 16:50:40 +00:00
Commented Nov 14 at 16:50 — Frank Ball
– Frank Ball, Commented Nov 14 at 16:50

Atmo · Accepted Answer · 2025-11-14 19:18:33Z

0

Excel does have a functionality to do it:

Which in VBA translates into the Range.Group method

It is used when selecting the first data cell of your time column (i.e. the one right under the header) in that fashion (as per the doc, the third parameter in the Periods array is the hours, but you may want to group by day, month and year too if you manipulate datetimes):

Sub Group()
    ActiveSheet.PivotTables(1).RowRange.Cells(2, 1).Group Start:=True, End:=True, _
        Periods:=Array(False, False, True, False, False, False, False)
End Sub

And to ungroup:

Sub Ungroup()
    ActiveSheet.PivotTables(1).RowRange.Cells(2, 1).Ungroup
End Sub

From there, adapt the code to get the right column from the RowRange in your sheet and possibly format the numbers to suit the display you want to have.

answered Nov 14 at 19:18

Atmo

4,2621 gold badge7 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to group time values by hour (not minute) in Excel PivotTable using VBA?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related