Using dates as data in Pivot Tables

[Guest]

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]

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.

 

[Guest]

Thanks Debra, I tried your suggestion but I still get the same results !

 

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

 

[Guest]

No, I tried that before.

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?

 

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

 

[Guest]

Yes they change correctly ! Thank you for your patience with this tho'

 

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

 

[Roger Govier]

Hi Deb

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