Help with GETPIVOTDATA and DATE

[datacrunchie]

I am looking up data using GETPIVOTDATA, using a number of parameters including country, product name and date. The GETPIVOTDATA formula automatically looks up the appropriate country, product name but when I try to automate the DATE there is a #REF! error. The GETPIVOTDATA formula uses DATE(2013,6,30) for example.

Please could someone help with this?

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

The expression =DATE(2013,6,30) returns the number 41,455 which is the serial date value Excel uses (the number of days since 1/1/1900).
You can put that expression in a cell and format the cell as a number to confirm that.

So based on the parameters you have available, you can replace DATE(2013,6,30) with any expression that will return the serial date value for the date of interest.

If A1 contains the serial date displayed as 6/30/13 you could just replace DATE(2013,6,30) with A1 in your GETPIVOTDATA formula
If A1 = 2013, B1 = 6, and C1 = 30 you could replace DATE(2013,6,30) with DATE(A1,B1,C1) in your GETPIVOTDATA formula

If you have the text value "6/30/2013" in Cell X1, you could replace DATE(2013,6,30) with DATEVALUE(X1) in your GETPIVOTDATA formula