# GETPIVOTDATA #REF for Date



## masplin (Apr 29, 2012)

Struggling to convert the fixed date form the GETPIVOTDATA function into a floating date. C22 contains the intial formula for Cell D27 in the pivot table.  In D22 I have changed the "Affilaite" tag to a floating ref to cell A22 and works fine.  I cannot tell  if the pivot table date in D25 is TEXT or a date so I entered 'March 2012 in cell A21 so also text. However changing the GETPIVOTDATA formula from 2012-3-1 T00:00:00 to A21 just shows #REF. I then tried a date in A21 1/3/2012 00:00:00 but no luck.

Can anyone can tell me how to use a floating reference to the date fields i.e. what to put in A21 that works?

Thanks for any advice

Mike









<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td bgcolor="#C0C0C0"> 
</td> <td align="center" bgcolor="#C0C0C0">*A*</td><td align="center" bgcolor="#C0C0C0">*B*</td><td align="center" bgcolor="#C0C0C0">*C*</td><td align="center" bgcolor="#C0C0C0">*D*</td><td align="center" bgcolor="#C0C0C0">*E*</td></tr> <tr><td align="center" bgcolor="#C0C0C0">*21*</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">March 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td></tr> <tr><td align="center" bgcolor="#C0C0C0">*22*</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">Affiliate </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">=GETPIVOTDATA("[Measures].[Count of User ID]",$A$24,"[Months Reverse Horiz]","[users].[Reg Month].&[2012-03-01T00:00:00]","[users].[Code Source]","[users].[Code Source].&[Affiliate]") </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">=GETPIVOTDATA("[Measures].[Count of User ID]",$A$24,"[Months Reverse Horiz]","[users].[Reg Month].&[2012-03-01T00:00:00]","[users].[Code Source]","[users].[Code Source].&["&A22&"]") </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">=GETPIVOTDATA("[Measures].[Count of User ID]",$A$24,"[Months Reverse Horiz]","[users].[Reg Month].&["&A21&"]","[users].[Code Source]","[users].[Code Source].&["&A22&"]") </td></tr> <tr><td align="center" bgcolor="#C0C0C0">*23*</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td></tr> <tr><td align="center" bgcolor="#C0C0C0">*24*</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">
</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">Reg Month </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">
</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td></tr> <tr><td align="center" bgcolor="#C0C0C0">*25*</td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">
</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">April 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">March 2012 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">
</td></tr> <tr><td align="center" bgcolor="#C0C0C0">*26*</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">code Source </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">Registrations </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">Activated </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">Registrations </td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">Activated </td></tr> <tr><td align="center" bgcolor="#C0C0C0">*27*</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">Affiliate </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">246 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">94 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">76 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">33 </td></tr> <tr><td align="center" bgcolor="#C0C0C0">*28*</td><td rowspan="1" colspan="1" align="left" bgcolor="#FFFFFF" valign="bottom" width="300" height="25.5">Corporate </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="273" height="25.5">183 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">5 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="255" height="25.5">92 </td><td rowspan="1" colspan="1" align="right" bgcolor="#FFFFFF" valign="bottom" width="183" height="25.5">5 </td></tr> </tbody></table>


----------



## masplin (Apr 29, 2012)

Worked it out. It is text, but in a date looking format so 

```
=TEXT(A20,"yyyy-mm-ddThh:mm:ss")
```

Where A20 is a date 1/3/2012


----------



## StewartS (Sep 6, 2012)

Thanks this helped me


----------



## jadhava15 (Dec 11, 2015)

StewartS said:


> Thanks this helped me



Hi,

Can you please tell me where did you put the Text formula exactly. I am facing the same issue of #ref error.
Thanks in advance.


----------



## masplin (Dec 11, 2015)

Hi The formula I had was


```
[COLOR=#000000]=GETPIVOTDATA("[Measures].[Count of User ID]",$A$24,"[Months Reverse Horiz]","[users].[Reg Month].&["&A21&"]","[users].[Code Source]","[users].[Code Source].&["&A22&"]")[/COLOR]
```

Where A21 was a cell that looked like it contained a date e.g. 01-Jan-15, but really it was text not a real date (i.e a number)

So I replaced the A21 with TEXT(A21,yyyy-mm-dd hh:mm:ss) so it created a date in the formatt he GETPIVOTDATA formula was expecting i.e. &["&A21&"] became  &["&TEXT(A21,yyyy-mm-dd hh:mm:ss)&"]


----------



## jadhava15 (Dec 11, 2015)

masplin said:


> Hi The formula I had was
> 
> 
> ```
> ...




Thanks masplin for quick response.
Got my formula working. I missed [&" thing in formula.


----------

