Excel Pivot Group by Date VBA Code Start/End Date Format

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
215
Office Version
  1. 2019
Platform
  1. Windows
VBA is refusing to use "dd/mm/yyyy" for VBA code for grouping by date.

VBA Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.Group Start:=44353, End:=44534, By:=7, Periods:=Array(False, _
        False, False, True, False, False, False)
End Sub

Sub Macro11()
'
' Macro1 Macro
'

'
    Selection.PivotTable.PivotFields("Date").ClearAllFilters
    Selection.PivotTable.PivotFields("Date").PivotFilters.Add2 _
        Type:=xlDateBetween, Value1:="06/06/2021", Value2:="04/12/21"
    Selection.Group Start:="06/06/2021", End:="12/04/2021", By:=7, Periods:=Array(False, _
        False, False, True, False, False, False)
End Sub

Macro1() shows me recording grouping by date by seven days starting from the 6th of June 2021 to the 4th of December 2021 on a pivot table.
Macro11() is me adding code to filter the pivot by date between the 6th of June 2021 to the 4th of December 2021 and changing the date serial number in the earlier recorded macro to proper dates.

For the date between filter or whenever I have used dates in past VBA code I have always used "dd/mm/yyyy" and had no issues but for the grouping code I had to use "mm/dd/yyyy" for the start/end dates. If I use "dd/mm/yyyy" I get an error when running the code that the end number must be greater than the start number, which I assume is the code thinking I put start 6th of June 2021 to end 12th of April 2021.

Both my Windows system region settings and my Excel cell format setting are set to use "dd/mm/yyyy" so I am not sure why the grouping code won't.

excelhelp vba group date1.jpg
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
72,982
Office Version
  1. 365
Platform
  1. Windows
VBA is US centric, so tends to converts dates to US dates where possible. Because of this I sugest you use date serial numbers, rather than actual dates, as it's safer
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,530
Office Version
  1. 365
Platform
  1. Windows
This format worked for me.
CDate uses your region setting.

VBA Code:
Sub FilterAndGroupOnDates()
    Selection.PivotTable.PivotFields("Date").PivotFilters.Add2 _
        Type:=xlDateBetween, Value1:=CLng(CDate("06/06/2021")), Value2:=CLng(CDate("04/12/21"))
    Selection.Group Start:=CLng(CDate("06/06/2021")), End:=CLng(CDate("04/12/21")), By:=7, Periods:=Array(False, _
        False, False, True, False, False, False)
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,463
Messages
5,831,783
Members
430,088
Latest member
meagerd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top