+ Reply to Thread
Results 1 to 2 of 2

#REF with Date with GETPIVOTDATA

  1. #1
    Registered User
    Join Date
    07-09-2018
    Location
    Stockholm, Sweden
    MS-Off Ver
    2016
    Posts
    1

    Lightbulb #REF with Date with GETPIVOTDATA

    I have a row with dates, 2018-01-01, 2018-02-01 ... 2020-12-01, that I want to reference to, in a GETPIVOTDATA formula. I've managed to reference to another cells for Gross Profit, AM, Region, and Team, but can't figure out the Month.

    GETPIVOTDATA("[Measures].[Sum of Gross Profit]";$BD$4;"[Data].[AM]";"[Data].[AM].&["&$A$4&"]";"[Data].[Month]";"[Data].[Month].&[2018-01-01T00:00:00]";"[Data].[Region]";"[Data].[Region].&["&$A$1&"]";"[Data].[Team]";"[Data].[Team].&["&$A$2&"]")

    I believe it has something to do with T00:00:00 but don't know what to do in the GETPIVOTFORMULA formula.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: #REF with Date with GETPIVOTDATA

    assuming real date value then you might try something like below as the parameter

    "[Data].[Month].&["&TEXT(B$1;"YYYY-MM-DD\Thh:mm:ss")&"]"

    modifying B$1 to be whichever cell is holding the date (assumed you're storing results in a matrix and formula will be copied to right)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. GETPIVOTDATA referencing a date cell
    By dbmagnus in forum Excel General
    Replies: 0
    Last Post: 03-21-2017, 06:39 PM
  2. [SOLVED] GETPIVOTDATA – How to force getpivotdata to accept missing data
    By scottc_00 in forum Excel Formulas & Functions
    Replies: 39
    Last Post: 02-11-2015, 03:23 PM
  3. Replies: 14
    Last Post: 06-11-2014, 03:32 PM
  4. [SOLVED] Getpivotdata for a date range criteria
    By kanonathena in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 09-18-2013, 08:46 PM
  5. getpivotdata for date
    By munna123 in forum Excel General
    Replies: 1
    Last Post: 06-17-2011, 12:57 PM
  6. Getpivotdata
    By cmb80 in forum Excel General
    Replies: 1
    Last Post: 11-12-2010, 11:25 AM
  7. getPivotData
    By KyWilde in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-06-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1