excel pivot tables

pocho2 · May 23rd, 2002, 09:03 AM

Any help on the subject would be greatly appreciated.

I have created a pivot table that sorts information by occurrence and date. In the date pull down you can chose the date range by checking the boxes. The problem is that in that pull down menu I do not want the date to be shown as 1/2/97(mm/dd/yy) I only need 1/97(mm/yy). I only care about how often and occurrence happens in a month of a certain year not the day. Is their a way to edit that information in the pull down menu?

Thanks,
Pocho

Mark W. · May 23rd, 2002, 09:16 AM

Can you add a column to your source data that uses the formula...

=TEXT(A2,"yyyy-mm")

where column A:A is your date field? This new column could be added your PivotTable and then used to select the month/year of interest.

pocho2 · May 23rd, 2002, 09:42 AM

Subject: pivot tables pull down menu

I can edit the format of the columns to apear anyway I want it example: m/d/y, d/y
where I am having the problem is in the way the pull down menu displays the dates. It displays them as m/d/y. Do you know how to change that so it displayes m/y within that pull down.

thanks,

Pocho

Mark W. · May 23rd, 2002, 10:04 AM

I understand; however, the Show/Hide list will always show a field's item as they are formatted in the PivotTable. So... you can't have it both "ways". Furthermore you can only use a field button once -- in the ROW, COLUMN or PAGE areas. That's why I was recommending a new field.

[ This Message was edited by: Mark W. on 2002-05-23 09:05 ]

pocho2 · May 23rd, 2002, 11:26 AM

Mark W. I miss understude what you said. It works. Thanks for the prompt reply. I only have one more question. The pivot table that I have has about 250 dated entries, is their a way for me to set up all of those entries so I do not have to go to each one and type =TEXT(A2,"YY-MM") for each entry?

Thanks,
Pocho

Mark W. · May 23rd, 2002, 12:02 PM

=TEXT(A2,"yyyy-mm") should be entered (and, copied down as far as needed) as a new field (column) in your data list -- not the PivotTable.

pocho2 · May 23rd, 2002, 06:24 PM

Mark W. thanks for the help and your prompt reply, your answers were exactly what I needed.

Pocho.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 23rd, 2002, 09:03 AM	#1
pocho2 New Member Join Date: May 2002 Location: pocho Posts: 6	Any help on the subject would be greatly appreciated. I have created a pivot table that sorts information by occurrence and date. In the date pull down you can chose the date range by checking the boxes. The problem is that in that pull down menu I do not want the date to be shown as 1/2/97(mm/dd/yy) I only need 1/97(mm/yy). I only care about how often and occurrence happens in a month of a certain year not the day. Is their a way to edit that information in the pull down menu? Thanks, Pocho

May 23rd, 2002, 09:16 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Can you add a column to your source data that uses the formula... =TEXT(A2,"yyyy-mm") where column A:A is your date field? This new column could be added your PivotTable and then used to select the month/year of interest.

May 23rd, 2002, 09:42 AM	#3
pocho2 New Member Join Date: May 2002 Location: pocho Posts: 6	Subject: pivot tables pull down menu I can edit the format of the columns to apear anyway I want it example: m/d/y, d/y where I am having the problem is in the way the pull down menu displays the dates. It displays them as m/d/y. Do you know how to change that so it displayes m/y within that pull down. thanks, Pocho

May 23rd, 2002, 10:04 AM	#4
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	I understand; however, the Show/Hide list will always show a field's item as they are formatted in the PivotTable. So... you can't have it both "ways". Furthermore you can only use a field button once -- in the ROW, COLUMN or PAGE areas. That's why I was recommending a new field. [ This Message was edited by: Mark W. on 2002-05-23 09:05 ]

May 23rd, 2002, 11:26 AM	#5
pocho2 New Member Join Date: May 2002 Location: pocho Posts: 6	Mark W. I miss understude what you said. It works. Thanks for the prompt reply. I only have one more question. The pivot table that I have has about 250 dated entries, is their a way for me to set up all of those entries so I do not have to go to each one and type =TEXT(A2,"YY-MM") for each entry? Thanks, Pocho

May 23rd, 2002, 12:02 PM	#6
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	=TEXT(A2,"yyyy-mm") should be entered (and, copied down as far as needed) as a new field (column) in your data list -- not the PivotTable.

May 23rd, 2002, 06:24 PM	#7
pocho2 New Member Join Date: May 2002 Location: pocho Posts: 6	Mark W. thanks for the help and your prompt reply, your answers were exactly what I needed. Pocho.