Results 1 to 3 of 3

Summarize/pivot with dates as column headers

This is a discussion on Summarize/pivot with dates as column headers within the Easy-XL Add-In forums, part of the MrExcel Products category; I have a worksheet that contains dates as some of the headers. When I select Summarize/Pivot, I see Jan282010 in ...

  1. #1
    Board Regular ExcelFanatic's Avatar
    Join Date
    May 2009
    Location
    Coronado, CA
    Posts
    77

    Default Summarize/pivot with dates as column headers

    I have a worksheet that contains dates as some of the headers. When I select Summarize/Pivot, I see Jan282010 in both the Columns to Analyze and Columns to Summarize windows, and not the dates I have on my spreadsheet.

    Is there a way I can see my headers and have it summarize properly. Right now, wheter I chose 1 or all of the Jan282010 references, the summary sheet shows them all as 2/10/2010.Total, and the data on the summary sheet is incorrect.
    Excel Fanatic

  2. #2
    Board Regular Easy-XL Support's Avatar
    Join Date
    Nov 2009
    Location
    Ottawa, Canada
    Posts
    133

    Default Re: Summarize/pivot with dates as column headers

    Easy-XL treats row 1 as column headers. Internally, Easy-XL is very much like a database system so when referring to columns by name there may be no arithmetic operators (+-*/^) in the names and names must start with a letter.

    If Easy-XL sees any of these operators in the column headers it removes them internally so when you are presented with a list of column names to choose from, you will see the column names as they are used internally. Additionally, if date cells are encountered for column names, Easy-XL converts the date to a text representation and uses that internally.

    To get around this, change the data contents (not cell format) of the column header to a fixed text value without any arithmetic operators in it and make sure it starts with a letter or underscore.

    I hope this helps.
    Last edited by Easy-XL Support; Jan 29th, 2010 at 07:58 AM. Reason: Paragraph spacing

  3. #3
    Board Regular
    Join Date
    Dec 2005
    Location
    Basingstoke (UK)
    Posts
    2,033

    Default Re: Summarize/pivot with dates as column headers

    Dates in headers are not good for pivot tables.
    Have a look at Podcast #493 at the following link to see one way to solve that problem:
    http://www.mrexcel.com/Excel_Non-Piv..._training.html
    Never give way to anger - otherwise in one day you could burn up the wood that you collected in many bitter weeks.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com