excel - Filtering between two dates in pivot table using VBA. UK to US date format issue

Question

I have found a workaround for this but if anyone has a cleaner way of doing this I'd be interested/grateful.

I have two boxes on a spreadsheet, called DateFrom and DateTo for use with a Date filter in a pivot table. Both are in the format DD/MM/YYYY. The data feeding into the table is also in this format. The pivot table also returns the dates in the same format. All my settings are in English UK and I've tried this on a separate machine with the same results.

I then use the following code to feed into the pivot table:

ActiveSheet.PivotTables("Pivot").PivotFields("Date").PivotFilters. _
    Add Type:=xlDateBetween, Value1:=Range("DateFrom").Value, Value2:=Range("DateTo").Value

The code reads the two cells as string and then applies this to the pivot table as if they were US format (MM/DD/YYYY). Ridiculous!

This is how I got around it:

I had two more cells looking at these two cells in numeric format and aimed the code at these two cells instead. Not sure about any of you but I would struggle to convert any date into numeric in my head...!

If you have a simplified solution and/or explanation to this utter craziness I'd be much obliged!

Answer 1

Indeed, this is strange. I've reproduced your issue with Excel 2007 and German locale settings.

However, when converting the date values to Longs using CLng, everything works:

ActiveSheet.PivotTables("Pivot").PivotFields("Date").PivotFilters. _
Add Type:=xlDateBetween, Value1:=CLng(Range("DateFrom").Value), Value2:=CLng(Range("DateTo").Value)