|
Using dates as data in Pivot Tables
Hi
I have the follwoing data in a spreadsheet Emp Event Date 1 A 01.01.06 1 B 01.02.06 2 A 01.01.06 3 B 01.03.06 I would like to use a pivot table to create the following :- Event A B Emp 1 01.01.06 01.02.06 2 01.01.06 3 01.03.06 Instead I get :- Count of Date Event Emp A B Grand Total 1 1 1 2 2 1 1 3 1 1 Grand Total 2 2 4 If I go into field settings I can change the date to show sum or count etc. but I would like to just display the date. Is this possible ? Any help appreciated. Thanks |
Using dates as data in Pivot Tables
Those don't look like real dates (e.g. 12/12/2006), so Excel treats them
as text. In the pivot table's data area, text from the source data isn't displayed. On a copy of your worksheet, you could try replacing all the periods with forward slashes, and create a pivot table from the revised data. Jill1 wrote: Hi I have the follwoing data in a spreadsheet Emp Event Date 1 A 01.01.06 1 B 01.02.06 2 A 01.01.06 3 B 01.03.06 I would like to use a pivot table to create the following :- Event A B Emp 1 01.01.06 01.02.06 2 01.01.06 3 01.03.06 Instead I get :- Count of Date Event Emp A B Grand Total 1 1 1 2 2 1 1 3 1 1 Grand Total 2 2 4 If I go into field settings I can change the date to show sum or count etc. but I would like to just display the date. Is this possible ? Any help appreciated. Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Using dates as data in Pivot Tables
Thanks Debra, I tried your suggestion but I still get the same results !
"Debra Dalgleish" wrote: Those don't look like real dates (e.g. 12/12/2006), so Excel treats them as text. In the pivot table's data area, text from the source data isn't displayed. On a copy of your worksheet, you could try replacing all the periods with forward slashes, and create a pivot table from the revised data. Jill1 wrote: Hi I have the follwoing data in a spreadsheet Emp Event Date 1 A 01.01.06 1 B 01.02.06 2 A 01.01.06 3 B 01.03.06 I would like to use a pivot table to create the following :- Event A B Emp 1 01.01.06 01.02.06 2 01.01.06 3 01.03.06 Instead I get :- Count of Date Event Emp A B Grand Total 1 1 1 2 2 1 1 3 1 1 Grand Total 2 2 4 If I go into field settings I can change the date to show sum or count etc. but I would like to just display the date. Is this possible ? Any help appreciated. Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Using dates as data in Pivot Tables
If you change the data field's summary function to Max, instead of Sum,
and format the data field as Date, do you get the results you want? Jill1 wrote: Thanks Debra, I tried your suggestion but I still get the same results ! "Debra Dalgleish" wrote: Those don't look like real dates (e.g. 12/12/2006), so Excel treats them as text. In the pivot table's data area, text from the source data isn't displayed. On a copy of your worksheet, you could try replacing all the periods with forward slashes, and create a pivot table from the revised data. Jill1 wrote: Hi I have the follwoing data in a spreadsheet Emp Event Date 1 A 01.01.06 1 B 01.02.06 2 A 01.01.06 3 B 01.03.06 I would like to use a pivot table to create the following :- Event A B Emp 1 01.01.06 01.02.06 2 01.01.06 3 01.03.06 Instead I get :- Count of Date Event Emp A B Grand Total 1 1 1 2 2 1 1 3 1 1 Grand Total 2 2 4 If I go into field settings I can change the date to show sum or count etc. but I would like to just display the date. Is this possible ? Any help appreciated. Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Using dates as data in Pivot Tables
No, I tried that before.
"Debra Dalgleish" wrote: If you change the data field's summary function to Max, instead of Sum, and format the data field as Date, do you get the results you want? Jill1 wrote: Thanks Debra, I tried your suggestion but I still get the same results ! "Debra Dalgleish" wrote: Those don't look like real dates (e.g. 12/12/2006), so Excel treats them as text. In the pivot table's data area, text from the source data isn't displayed. On a copy of your worksheet, you could try replacing all the periods with forward slashes, and create a pivot table from the revised data. Jill1 wrote: Hi I have the follwoing data in a spreadsheet Emp Event Date 1 A 01.01.06 1 B 01.02.06 2 A 01.01.06 3 B 01.03.06 I would like to use a pivot table to create the following :- Event A B Emp 1 01.01.06 01.02.06 2 01.01.06 3 01.03.06 Instead I get :- Count of Date Event Emp A B Grand Total 1 1 1 2 2 1 1 3 1 1 Grand Total 2 2 4 If I go into field settings I can change the date to show sum or count etc. but I would like to just display the date. Is this possible ? Any help appreciated. Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Using dates as data in Pivot Tables
Your dates must still be recognized as text, instead of real dates. If
you select the dates in the source data, and change the date format (FormatCells), e.g. 15-Nov-06, do the cells change? Jill1 wrote: No, I tried that before. "Debra Dalgleish" wrote: If you change the data field's summary function to Max, instead of Sum, and format the data field as Date, do you get the results you want? Jill1 wrote: Thanks Debra, I tried your suggestion but I still get the same results ! "Debra Dalgleish" wrote: Those don't look like real dates (e.g. 12/12/2006), so Excel treats them as text. In the pivot table's data area, text from the source data isn't displayed. On a copy of your worksheet, you could try replacing all the periods with forward slashes, and create a pivot table from the revised data. Jill1 wrote: Hi I have the follwoing data in a spreadsheet Emp Event Date 1 A 01.01.06 1 B 01.02.06 2 A 01.01.06 3 B 01.03.06 I would like to use a pivot table to create the following :- Event A B Emp 1 01.01.06 01.02.06 2 01.01.06 3 01.03.06 Instead I get :- Count of Date Event Emp A B Grand Total 1 1 1 2 2 1 1 3 1 1 Grand Total 2 2 4 If I go into field settings I can change the date to show sum or count etc. but I would like to just display the date. Is this possible ? Any help appreciated. Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Using dates as data in Pivot Tables
Yes they change correctly ! Thank you for your patience with this tho'
"Debra Dalgleish" wrote: Your dates must still be recognized as text, instead of real dates. If you select the dates in the source data, and change the date format (FormatCells), e.g. 15-Nov-06, do the cells change? Jill1 wrote: No, I tried that before. "Debra Dalgleish" wrote: If you change the data field's summary function to Max, instead of Sum, and format the data field as Date, do you get the results you want? Jill1 wrote: Thanks Debra, I tried your suggestion but I still get the same results ! "Debra Dalgleish" wrote: Those don't look like real dates (e.g. 12/12/2006), so Excel treats them as text. In the pivot table's data area, text from the source data isn't displayed. On a copy of your worksheet, you could try replacing all the periods with forward slashes, and create a pivot table from the revised data. Jill1 wrote: Hi I have the follwoing data in a spreadsheet Emp Event Date 1 A 01.01.06 1 B 01.02.06 2 A 01.01.06 3 B 01.03.06 I would like to use a pivot table to create the following :- Event A B Emp 1 01.01.06 01.02.06 2 01.01.06 3 01.03.06 Instead I get :- Count of Date Event Emp A B Grand Total 1 1 1 2 2 1 1 3 1 1 Grand Total 2 2 4 If I go into field settings I can change the date to show sum or count etc. but I would like to just display the date. Is this possible ? Any help appreciated. Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Using dates as data in Pivot Tables
In the pivot table, is Employee in the Row area, Event in the Column
area, and Date in the Data area, as Max of Date? Jill1 wrote: Yes they change correctly ! Thank you for your patience with this tho' "Debra Dalgleish" wrote: Your dates must still be recognized as text, instead of real dates. If you select the dates in the source data, and change the date format (FormatCells), e.g. 15-Nov-06, do the cells change? Jill1 wrote: No, I tried that before. "Debra Dalgleish" wrote: If you change the data field's summary function to Max, instead of Sum, and format the data field as Date, do you get the results you want? Jill1 wrote: Thanks Debra, I tried your suggestion but I still get the same results ! "Debra Dalgleish" wrote: Those don't look like real dates (e.g. 12/12/2006), so Excel treats them as text. In the pivot table's data area, text from the source data isn't displayed. On a copy of your worksheet, you could try replacing all the periods with forward slashes, and create a pivot table from the revised data. Jill1 wrote: Hi I have the follwoing data in a spreadsheet Emp Event Date 1 A 01.01.06 1 B 01.02.06 2 A 01.01.06 3 B 01.03.06 I would like to use a pivot table to create the following :- Event A B Emp 1 01.01.06 01.02.06 2 01.01.06 3 01.03.06 Instead I get :- Count of Date Event Emp A B Grand Total 1 1 1 2 2 1 1 3 1 1 Grand Total 2 2 4 If I go into field settings I can change the date to show sum or count etc. but I would like to just display the date. Is this possible ? Any help appreciated. Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Using dates as data in Pivot Tables
Hi Deb
Taking Jill's data and following your instruction works fine for me and produces exactly what Jill says she requires. -- Regards Roger Govier "Debra Dalgleish" wrote in message ... In the pivot table, is Employee in the Row area, Event in the Column area, and Date in the Data area, as Max of Date? Jill1 wrote: Yes they change correctly ! Thank you for your patience with this tho' "Debra Dalgleish" wrote: Your dates must still be recognized as text, instead of real dates. If you select the dates in the source data, and change the date format (FormatCells), e.g. 15-Nov-06, do the cells change? Jill1 wrote: No, I tried that before. "Debra Dalgleish" wrote: If you change the data field's summary function to Max, instead of Sum, and format the data field as Date, do you get the results you want? Jill1 wrote: Thanks Debra, I tried your suggestion but I still get the same results ! "Debra Dalgleish" wrote: Those don't look like real dates (e.g. 12/12/2006), so Excel treats them as text. In the pivot table's data area, text from the source data isn't displayed. On a copy of your worksheet, you could try replacing all the periods with forward slashes, and create a pivot table from the revised data. Jill1 wrote: Hi I have the follwoing data in a spreadsheet Emp Event Date 1 A 01.01.06 1 B 01.02.06 2 A 01.01.06 3 B 01.03.06 I would like to use a pivot table to create the following :- Event A B Emp 1 01.01.06 01.02.06 2 01.01.06 3 01.03.06 Instead I get :- Count of Date Event Emp A B Grand Total 1 1 1 2 2 1 1 3 1 1 Grand Total 2 2 4 If I go into field settings I can change the date to show sum or count etc. but I would like to just display the date. Is this possible ? Any help appreciated. Thanks -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| All times are GMT +1. The time now is 03:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com