Dynamic GetPivotData Formula to Change Date Referenced

[EnergyExcel]

Hello,

I am utilizing a GetPivotData formula and need to make the date refenced dynamic to change it month over month for analysis. Currently the date from the GetPivotData is 2022-09-30T00:00:00 but I want to reference a specific cell outside of the pivot (lets say A5) that will contain the date I want. Below is the formula I currently have directly from GetPivotData:

=GETPIVOTDATA("[Measures].[Sum of NET VALUE - VOLUME]",'Rev & Vol by Operator'!$A$1,"[REVENUE DETAIL].[PRODUCT CODE]","[REVENUE DETAIL].[PRODUCT CODE].&[GAS]","[REVENUE DETAIL].[Operator Name]","[REVENUE DETAIL].[Operator Name].&["&$A9&"]","[REVENUE DETAIL].[EOM CASH DATE]","[REVENUE DETAIL].[EOM CASH DATE].&[2022-09-30T00:00:00]")

I tried replacing [2022-09-30T00:00:00] with ["&$A5&"] but I am getting a REF error. I was able to successfully replace the Operator with a cell reference (A9) so not sure if it is a formatting thing or not.

Thanks for any help!

 

[EnergyExcel]

SOLVED:
I was able to solve this by using my reference date and converting the date to the GetPivotData date format:

["&CONCATENATE(TEXT(A5,"YYYY"),"-",TEXT(A5,"MM"),"-",TEXT(A5,"DD"),"T00:00:00")&"]

 

[RoryA]

FWIW, you can do it all with one TEXT function:

["&TEXT(A5,"YYYY-MM-DD""T00:00:00""")&"]