GETPIVOTDATA with date in referenced cell

[irchrisb]

Hi. Using xl2000 on XP.

I've written the formula =IF(ISNA(GETPIVOTDATA(threewtpt,$A$2&" "&
$A4&" "&$C$16))=TRUE,0,GETPIVOTDATA(threewtpt,$A$2&" "&$A4&" "&$C$16))

When I enter a date in $C$16, referring to a field containing date
items, the formula returns an incorrect result of $0.00. When I
change the field to one containing text and enter a corresponding text
item in $C$16, the formula returns the correct result of $5.00. If I
write the formula referring to the items using text, e.g.
=GETPIVOTDATA(threewtpt,"'Seattle' 'BHL' Date[1/1/2008]), instead of
cell references, it works just fine. I tried to exchange the $C$16
cell reference in the formula with Date[1/1/2008], but I get the same
result of $0.00. Is there something I can do differently?

Thanks,
Chris

 

[Debra Dalgleish]

Try changing the date to text, formatted the same as the date in the
pivot table. For example:

GETPIVOTDATA(threewtpt,$A$2&" "&$A4&" "& TEXT($C$16,"m/d/yyyy"))

Hi. Using xl2000 on XP.

I've written the formula =IF(ISNA(GETPIVOTDATA(threewtpt,$A$2&" "&
$A4&" "&$C$16))=TRUE,0,GETPIVOTDATA(threewtpt,$A$2&" "&$A4&" "&$C$16))

When I enter a date in $C$16, referring to a field containing date
items, the formula returns an incorrect result of $0.00. When I
change the field to one containing text and enter a corresponding text
item in $C$16, the formula returns the correct result of $5.00. If I
write the formula referring to the items using text, e.g.
=GETPIVOTDATA(threewtpt,"'Seattle' 'BHL' Date[1/1/2008]), instead of
cell references, it works just fine. I tried to exchange the $C$16
cell reference in the formula with Date[1/1/2008], but I get the same
result of $0.00. Is there something I can do differently?

Thanks,
Chris