locked
XL 2010 Pivot Table with grouped date field RRS feed

  • Question

  • I am unable to display a date column in a pivot table in true date format once it's been grouped by Year for the Year-to-date running calculation on some numeric data columns.
    I even tried to create a calculated field from the date field, but it displayed as 0 : 1/0/1900. Since there were no Column Fields, I am unable to create a Calculated Item.
    Once grouped, the date column appears as 2011, with Jan, Feb, Mar, etc. showing as the actual date when in fact in the source data table the value is 1/1/2011, 2/1/2011, etc.
    I need the proper date format for a FIND/MATCH lookup. I was really surprised when in VBA the ActiveCell.Value came up as a literal : "Jan", "Feb", etc.

    Any workaround to this behavior ? I am looking to perform a search on the pivot table to be able to perform flexible variance analysis....i.e. compare any cell to any other cell. The built-in technique for doing this just is not powerful enough.

    Any ideas greatly appreciated.

    Saturday, June 23, 2012 2:51 PM

Answers

  • Excel 2010 PivotTable
    Once you group a date PivotField, all the Items are irreversibly formatted as text.
    With a macro, you can relabel each Item with a proper WRS date string,
    based on the original content.
    Outside the PT, DateValue() can then convert the PivotField entry into a regular date.
    http://c3017412.r12.cf0.rackcdn.com/02_11_11.xlsm
    If you get *.zip, don't unzip, just rename *.xlsm

    • Marked as answer by Syswizard Sunday, June 24, 2012 9:15 PM
    Saturday, June 23, 2012 11:50 PM