Excel, Pivot, Dates.

March 25, 2009 11:03 AM

Excel - Pivoting With Dates

Dear Pivot Table Pros,

I frequently do pivots of counts vs time.

• Logins vs time.
• Sales vs time.
• Website hits to our sitemap by time.

My data can be commonly simplified to DATESTAMP | VALUE format. Usually I need to add a =day(), =month(), =year() column to my data to pivot it our by those values. Does Excel have some other way to help me do this? I would like to retain the ability to toss in days of a month for granularity of data, or summarize by year.

posted by SirStan to Computers & Internet (3 answers total)

Once you add your raw datestamp to the pivot table, you should be able to rt-click and select the Group option. From that group menu, you can select Days, Months, Quarters, Years, etc. That will create new filter drop downs and grouping columns and you can rearrange and go from there.
posted by gatorbiddy at 11:21 AM on March 25, 2009

Response by poster: It says "cannot group that selection". Can I not just use dates (1-1-09)?
posted by SirStan at 6:10 AM on March 26, 2009

Well, googling that error message seems to imply that maybe you have blank data in your datestamp column. Not sure what you mean by Can I not just use dates....Yes that's what you are doing here right? You have a datestamp column in your data, and instead of making new columns for day, month, year in your data, you can use the group option to do that for you directly in your pivot table. Otherwise, check for blank cells in your datestamp column.

Also plenty of information out there on the search engines:
http://www.contextures.com/xlPivot07.html

http://support.microsoft.com/kb/214026
posted by gatorbiddy at 9:12 AM on March 26, 2009