使用 VBA 在数据透视表中的两个日期之间进行过滤.英国到美国的日期格式问题 [英] Filtering between two dates in pivot table using VBA. UK to US date format issue

问题描述

我已经找到了解决方法，但如果有人有更清洁的方法，我会很感兴趣/感激不尽.

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

我在电子表格上有两个框，称为 DateFrom 和 DateTo，用于数据透视表中的日期过滤器.两者都采用 DD/MM/YYYY 格式.输入表格的数据也是这种格式.数据透视表还以相同格式返回日期.我所有的设置都是英国英语，我已经在另一台机器上尝试过，结果相同.

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

代码将两个单元格读取为字符串，然后将其应用于数据透视表，就好像它们是美国格式 (MM/DD/YYYY).可笑！

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!

推荐答案

确实，这很奇怪.我已经重现了您在 Excel 2007 和德语区域设置方面的问题.

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

但是，当使用 CLng 将日期值转换为 Long 时，一切正常:

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)

这篇关于使用 VBA 在数据透视表中的两个日期之间进行过滤.英国到美国的日期格式问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！