4

I have a pivot table that I am trying to reference using a date as part of the lookup. The pivot table data can be grouped by day, week or month (there is a little VBA that controls this) However, I cannot seem to create a GETPIVOTDATA function that will work with all three groupings. - When grouped by day, GETPIVOTDATA seems to want the day of the year (1-365), it won't accept a date.

    =GETTPIVOTDATA(value, pt, "Date", 184)
  • When grouped by week, it will accept a date and return the week that the date falls in
    =GETTPIVOTDATA(value, pt, "Date", Date(2012, 08, 22))
  • When grouped by month, it seems to want the month of the year (1-12)
    =GETTPIVOTDATA(value, pt, "Date", 11)

Is there anyway to use a date with all three groupings? Bear in mind that the groupings will change but the formula won't so the same formula has to work with any grouping.

The alternatives are using an IF statement and date manipulation or using VBA whenever I change the groupings. However, I feel that the should be a way to just use the date, regardless of the grouping.

Improve this question
2
  • 1
    I would do as you suggested with an IF statement, however, but the statement in a separate cell that gives you the format you need based on the grouping, then reference that cell as the last argument in your GETPIVOTDATA formula. Commented Aug 23, 2012 at 14:18
  • 2
    Personally, I've used the CHOOSE statement over an IF to solve this problem. I have a set of Radio Buttons that select the date period with VBA to change the pivot table. The CHOOSE statement uses the output value from the radio buttons to slot the correct format into the GETPIVOTDATA statement. Commented Sep 21, 2012 at 9:43
Related questions
0
Excel pivot table using date column to filter results
0
Powerpivot incorrectly convert date value to text in pivot-table
2
Date Sorting in Excel PivotTables
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.