Skip to Content
author's profile photo Former Member
Former Member

Pivot table not reading Calendar Year/Month as Date


I am using Analysis for Office version. and Microsoft Office 2016 where I have SAP crosstab from the Data Source in one tab and pivot table based on it placed in another tab.

In the query/crosstab I have four dimensions where one of them is Calendar Year/Month in format YYYY-MM.

The problem that I am facing is that although the pivot table is reading data from crosstab it is not considering Calendar Year/Month as a date (printscreen 1). Therefore I cannot filter for dates filter like: current month, previous month - only by label value. My computer date format is set to be YYYY-MM-DD.

In the SAP crosstab where I place the cursor in the cell with Calendar Year/Month data I can see that data is written in format '2016-03 and not 2016-03 (printscreen 2). (The case is for all sap dimensions members.) If the sign " ' " is manually removed from each cell then system is automatically converting data into Mar-2016 and pivot table understands it as date.

I am trying to write a macro which is replacing " ' " with " " but Macro is not reading " ' " sign as existing in the cell since that is done automatically by SAP.

The result that I would like to get automatically is shown on printscreen 3. (here done by manual deleting of all " ' " from each cell of Calendar Year/Month)

Could you please help with understanding how to read Calendar Year/Month in Pivot Table as date.

Printscreen 3.png (43.4 kB)
Printscreen 2.png (11.0 kB)
Printscreen 1.png (19.2 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Jul 21, 2016 at 02:21 PM


    you have to use VBA and do magic 😉

    For example

    Dim b As Date

    b = Range("J12").Value

    Range("J12").Value = b

    In J12 is '01.01.2016 and after the macro run there is 01.01.2016 which is a date.

    Hope this helps.

    Best regards,


    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.