Displaying a date without including the individual day

[mpalermino76]

Hello - I'm trying to build a pivot table that looks at dates. I only want it to pull by month. The dates I have entered are forcing the pivot table to have tons of lines of data. I really only want 12 lines of data (January 2011 - December 2011). Does this make sense? Any help is greatly appreciated.

Thanks!
Marc

 

[doublej_jj]

Why not insert a column after your date to simply identify the month. If you date is in column a, insert column b with formula:
=month(A1) and copy down

If you would like to "pretty it up" a little and have the month name instead ofthe numbers 1 to 12, find some place to make a chart of month numbers and names and do a look up:

In AA1:AB12, put a column of 1 to 12 and Jan to Dec. Now B1 is
=Vlookup(Month(A1),$AA$1:$AB$12,2,0)

Hope this helps.

Jeff

 

[Desu Nota from Columbus]

Hello - I'm trying to build a pivot table that looks at dates. I only want it to pull by month. The dates I have entered are forcing the pivot table to have tons of lines of data. I really only want 12 lines of data (January 2011 - December 2011). Does this make sense? Any help is greatly appreciated.

Thanks!
Marc

Take a look at this--much easier than me typing it out.

http://www.mrexcel.com/articles/pivot-table-group-dates-by-month.php

**In Summary**

Right Click on the Date (pivot table button) and select group by months.

 

[pagrender]

Have you used the "Group" option of a pivot table? I know that it's available in Excel 2007 (not sure about earlier versions). YOu can group by months with this option. It's available in the Options Ribbon of your pivot table.

 

[mpalermino76]

The problem I am having is that I'm using Excel 2010 and I don't see where the dates can be converted over to Groups. There are also more than 256 different dates that the pivot table won't display to begin with. The other problem is that the date range starts in 2010 and goes to current. So using the month(A2) feature doesn't distinguish between (for example) the month of February for 2010 and February 2011. Ultimately, I would be looking for something that allows me to convert (example again):

1/3/2010
1/5/2010
1/13/2010
2/14/2011

would look like:
January 2010
January 2010
January 2010
February 2011

 

[pagrender]

Can you format the cells in the date column of the pivot table to make it look like you want?

Enter this as a custom format in the format cells dialog box:
mmmm yyyy

Using this method, this data turned the following:
<TABLE style="WIDTH: 69pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=92 border=0><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 69pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=92 height=20>1/3/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1/5/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>1/13/2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>2/14/2011</TD></TR></TBODY></TABLE>


into this:
<TABLE style="WIDTH: 71pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=94 border=0><COLGROUP><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3437" width=94><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 71pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=94 height=20>January 2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>January 2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>January 2010</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>February 2011</TD></TR></TBODY></TABLE>

 

[doublej_jj]

Sticking with the idea of inserting a column before creating the pivot table:

If dates are in Column A, then B1 is
=Month(A1) & " " & Year(A1) this returns 1 2011, 2 2011, etc

To fancy it up with the months {numbers and names} out in AA1:AB12
=Vlooukp(Month(A1),$AA$1:$AB$12,2,0)&" "&Year(A1)

This will give you what you asked for below [January 2011, etc]. However, the next complaint will probably be that they are out of date order and in alphabetic order and I am not sure how to solve that.

Jeff