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

[fhqwgads]

VBA is refusing to use "dd/mm/yyyy" for VBA code for grouping by date.

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.

 

[Fluff]

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]

This format worked for me.
CDate uses your region setting.

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