| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot Tables - Missing Data after refresh | Excel Worksheet Functions | |||
| pivot tables - how can i set one up with data points over time? | Charts and Charting in Excel | |||
| How do I compare data in two pivot tables | Excel Discussion (Misc queries) | |||
| Pivot table, dynamic data formula | Excel Discussion (Misc queries) | |||
| Pivot tables share source data "after the fact" | Excel Worksheet Functions | |||
Hybrid Mode
