PivotTableFilter
January 13, 2006 6:39 AM Subscribe
PivotTableFilter: Can I have columns by month if I have the full date?
What I'd like to do is using Pivot Tables show a count of records by month. If I drag the date column to the top of the pivot table I get a count of records by each specific date. There are records from almost every day in the last year in this table and I don't want 365 columns!
What I've done as a workaround is separate the dates out by using "text to columns" so I have a month column, a day column and a year column. Though, then I have to convert the number format of the column to "general" rather than "date" and can't very easily have month names in place of the numbers 1-12 (or sort them in date order versus alphabetical order). (Plus I've run into trouble with find/replace trying to switch the numbers.) This all seems needlessly complex. There must be a better way!
What I'd like to do is using Pivot Tables show a count of records by month. If I drag the date column to the top of the pivot table I get a count of records by each specific date. There are records from almost every day in the last year in this table and I don't want 365 columns!
What I've done as a workaround is separate the dates out by using "text to columns" so I have a month column, a day column and a year column. Though, then I have to convert the number format of the column to "general" rather than "date" and can't very easily have month names in place of the numbers 1-12 (or sort them in date order versus alphabetical order). (Plus I've run into trouble with find/replace trying to switch the numbers.) This all seems needlessly complex. There must be a better way!
Response by poster: Sorry, I'm using Excel 2003 on XP
posted by pithy comment at 6:55 AM on January 13, 2006
posted by pithy comment at 6:55 AM on January 13, 2006
Response by poster: So the Month() function works to get the month number. Is there a way to get the month in text form? I tried a custom number format for the cell: "mmm" - but somehow that makes everything "Jan"
posted by pithy comment at 7:45 AM on January 13, 2006
posted by pithy comment at 7:45 AM on January 13, 2006
You can get there from where you are at.
select the dates and any additional data. go to Data > pivot table. accept the defaults.
You'll see an empty pivit table layout and a selection window with the fields of data.
Select the date field and drag into the leftmost side of the layout, labeled "drop row fields here." Then select the date field again and drag it into the larger blank area of the layout, labeled "drop data items here".
That should do it. When you set up the first data element you should have seen excel only listing each date one time. as it did so it expected to be asked to produce a count some data associated with each date; by dragging the date column to the data items area you told excel to count incidences of date.
YMMV, natch.
posted by mwhybark at 7:51 AM on January 13, 2006
select the dates and any additional data. go to Data > pivot table. accept the defaults.
You'll see an empty pivit table layout and a selection window with the fields of data.
Select the date field and drag into the leftmost side of the layout, labeled "drop row fields here." Then select the date field again and drag it into the larger blank area of the layout, labeled "drop data items here".
That should do it. When you set up the first data element you should have seen excel only listing each date one time. as it did so it expected to be asked to produce a count some data associated with each date; by dragging the date column to the data items area you told excel to count incidences of date.
YMMV, natch.
posted by mwhybark at 7:51 AM on January 13, 2006
whoops, i misread the question - you're after one more step. one moment.
The steps I present should work with any of your subsetted date cols, but I think you can get there with any recognized date format as well, 01/10/2006 etc.
Right click on the left-hand column header, if the column was populated by full-length dates. go to "group and show detail > group > months"
That should recount and reformat standard dates by month.
posted by mwhybark at 7:59 AM on January 13, 2006
The steps I present should work with any of your subsetted date cols, but I think you can get there with any recognized date format as well, 01/10/2006 etc.
Right click on the left-hand column header, if the column was populated by full-length dates. go to "group and show detail > group > months"
That should recount and reformat standard dates by month.
posted by mwhybark at 7:59 AM on January 13, 2006
Response by poster: Hmmm. This "Group and Show Detail" thing seems promising. But here's what I've done:
Row field: I dragged "Categories"
Column fields: I dragged "Date"
Data Items: "Count of Date"
When I right click on the row field "Date" and select "group and show detail" it says "Cannot group that selection." Same thing happens if I swap the row/column data fields.
So far what I've done to workaround is just use jacquilynne's Month() suggestion in a new column. The only thing I'm missing there is the Long-form month name.
posted by pithy comment at 8:34 AM on January 13, 2006
Row field: I dragged "Categories"
Column fields: I dragged "Date"
Data Items: "Count of Date"
When I right click on the row field "Date" and select "group and show detail" it says "Cannot group that selection." Same thing happens if I swap the row/column data fields.
So far what I've done to workaround is just use jacquilynne's Month() suggestion in a new column. The only thing I'm missing there is the Long-form month name.
posted by pithy comment at 8:34 AM on January 13, 2006
Use a custom format of MMMM which should provide the full month name.
posted by evening at 9:22 AM on January 13, 2006
posted by evening at 9:22 AM on January 13, 2006
Use a custom format of MMMM which should provide the full month name.
No, that doesn't work for what pithy is trying to do, because:
a) if you use a custom format of MMMM on the original date, it displays only the month name, but internally it's still represented as the full date, so different dates from the same month still aren't combined in a pivot table.
b) if you use a custom format of MMMM on the result of the month() function, it tries to interpret the result of that function as a date; but the result of the month function is an integer between 1 and 12. When Excel interprets an integer as a date, the date is that of the date equal to that number of days after December 31, 1899, i.e., 1=>Jan. 1 1900; 2=> Jan. 2 1900; etc. So if you start with July 4, 2005, the month() function applied to that date returns 7. But if you force Excel to interpret "7" as a date (as you are when you apply a custom format of MMMM), it interprets that as January 7, 1900, so the MMMM format spits back "January," as pithy already noted above.
One way to do what pithy is trying to do is by applying the CHOOSE() function. If the original date is in A1, the name of the month is given by:
CHOOSE(MONTH(A1), "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
It's a bit kludgy to hardcode the names of the months in like that, I admit, but I don't know a better way.
posted by DevilsAdvocate at 10:04 AM on January 13, 2006
No, that doesn't work for what pithy is trying to do, because:
a) if you use a custom format of MMMM on the original date, it displays only the month name, but internally it's still represented as the full date, so different dates from the same month still aren't combined in a pivot table.
b) if you use a custom format of MMMM on the result of the month() function, it tries to interpret the result of that function as a date; but the result of the month function is an integer between 1 and 12. When Excel interprets an integer as a date, the date is that of the date equal to that number of days after December 31, 1899, i.e., 1=>Jan. 1 1900; 2=> Jan. 2 1900; etc. So if you start with July 4, 2005, the month() function applied to that date returns 7. But if you force Excel to interpret "7" as a date (as you are when you apply a custom format of MMMM), it interprets that as January 7, 1900, so the MMMM format spits back "January," as pithy already noted above.
One way to do what pithy is trying to do is by applying the CHOOSE() function. If the original date is in A1, the name of the month is given by:
CHOOSE(MONTH(A1), "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
It's a bit kludgy to hardcode the names of the months in like that, I admit, but I don't know a better way.
posted by DevilsAdvocate at 10:04 AM on January 13, 2006
Best answer: Here's another way to do it: create a new column, where, for example in row 1, if the original date is in A1:
DATE(2005, MONTH(A1), 1)
This new column will have each date converted to the first day of the given month in 2005. (So April 15, 2005, becomes April 1, 2005.) Apply the MMMM format to that column, and it will display just the month name, and it will also work like you want it to in the pivot table.
posted by DevilsAdvocate at 10:08 AM on January 13, 2006
DATE(2005, MONTH(A1), 1)
This new column will have each date converted to the first day of the given month in 2005. (So April 15, 2005, becomes April 1, 2005.) Apply the MMMM format to that column, and it will display just the month name, and it will also work like you want it to in the pivot table.
posted by DevilsAdvocate at 10:08 AM on January 13, 2006
Response by poster: Devil - thanks for the great suggestions and more importantly for giving me a wonderful new word, Kludgy!
posted by pithy comment at 10:34 AM on January 13, 2006
posted by pithy comment at 10:34 AM on January 13, 2006
Response by poster: So one other Excel-tip question....
I always like clicking on the bottom right-hand corner of a cell and dragging down to additional rows to copy a formula, like the one you linked above. Is there a better/more efficient way to do that? (My chart has 11491 rows.)
posted by pithy comment at 11:06 AM on January 13, 2006
I always like clicking on the bottom right-hand corner of a cell and dragging down to additional rows to copy a formula, like the one you linked above. Is there a better/more efficient way to do that? (My chart has 11491 rows.)
posted by pithy comment at 11:06 AM on January 13, 2006
- Highlight the cell you want to copy the formula from
- Ctrl-C
- Click in the first cell of the range you want to copy the formula into
- Scroll down to the last cell of the range you want to copy the formula into, and shift-click there.
- Ctrl-V
posted by DevilsAdvocate at 11:46 AM on January 13, 2006
A quicker way is to copy the cell with the formula, then click on the column header and press Shift-Insert. This will copy the formula to all of the cells in that column.
posted by SteveInMaine at 11:48 AM on January 13, 2006
posted by SteveInMaine at 11:48 AM on January 13, 2006
However, note that it will copy the formula to all 65536 cells in that column, not just the 11491 that are next to pithy's original data.
posted by DevilsAdvocate at 12:19 PM on January 13, 2006
posted by DevilsAdvocate at 12:19 PM on January 13, 2006
In a full column,
ctrl+down arrow keys to move to the bottom of your records
move to the empty column you'd like to fill with the equation
shift+ctrl+up arrow (the equation should be highlighted, along with the empty cells.
Fill with crtl+D
also keep in mind the "$" characters can be used in the equation to stabilize reference locations.
posted by eustatic at 1:43 PM on January 13, 2006
Response by poster: ctrl+D? I've never heard of that!
posted by pithy comment at 3:14 PM on January 13, 2006
posted by pithy comment at 3:14 PM on January 13, 2006
Best answer: You can also just double click on the bottom right corner of the cell. That will auto-fill as long as there is a value in the column to the left. If you have any blank spots or don't have a column to the left, that won't work.
posted by willnot at 5:14 PM on January 13, 2006
posted by willnot at 5:14 PM on January 13, 2006
This thread is closed to new comments.
All of which would be easier to determine if I knew what program you were using.
posted by jacquilynne at 6:50 AM on January 13, 2006