Using dates as data in Pivot Tables

J

Jill1

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
 
D

Debra Dalgleish

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.
 
D

Debra Dalgleish

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?
 
J

Jill1

No, I tried that before.

Debra Dalgleish said:
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?
Click to expand...
 
D

Debra Dalgleish

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
(Format>Cells), e.g. 15-Nov-06, do the cells change?
 
D

Debra Dalgleish

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?
 
R

Roger Govier

Hi Deb

Taking Jill's data and following your instruction works fine for me and
produces exactly what Jill says she requires.
 
Top