PivotTable, Dates, and VBA

[derpotheman]

Please Login or Register  to view this content.

Okay so I am getting EXTREMELY frustrated with this, the code should work by all logic I possess but excel is having one of those demonic do what it wants moments.

In my row fields on the pivot tables I have dates, the values are like this 4/1/2009, all the way through this month 4/1/2011, each month is represented as a row.

I am trying to go through and hide all fields that are more than 8 months behind todays month. The problem is when vba takes the value for the month out of the field list (clearly written into the formula bar as I showed above)

It changes (for example) 4/1/2009 to 4/9/2011. For whatever reason it seems to be blindly swapping the day and year values. Obviously this totally ruins any attempts I make at using the date difference to do anything useful, as well as butchering my row labels.

All the formats work fine if i do it manually (change the date format by right clicking etc). It literally CHANGES the value in the formula bar at this point in the code ".PivotItems(x).Value = Format(.PivotItems(x).Value, "m/dd/yyyy")"

Like I said it seems to be interpreting my days as years and vice versa, but why?

[DonkeyOte]

There are certainly some bugs re: Pivots & Dates XL2007+ (not fixed in XL2010 as far as I know)

Try the following... and in advance, yes, I appreciate this isn't ideal - but worth testing ?

1. format source dates as General

2. format Pivot date field as General

3. refresh PT
(for sake of clarity it is probably worth setting PT Options -> Data -> Retained Items to None)

4. revert Pivot date field format to Date (optional)

5. modify VBA to account for "(blank)" item and explicitly cast criteria to Date (CDate)

downside - you must keep source dates in General format.

similar issue from which the above is adapted: http://www.mrexcel.com/forum/showthread.php?t=518846 ... posts 9 & 10 outline issue and workaround.

[derpotheman]

Thanks a lot that adds a lot of clarity to this issue, I thought I was just doing something dumb.


Once again, big thanks! I'll see if this works, if not I'm just going to try to find some other way to avoid referencing these dates.

I'm just going to call this solved as well