Excel Pivot Table Max Date

[GPnew]

I am trying to create a pivot table to show in the grouping , the last (ie maximum) payment made by a debtor who trades with several companies (NAME) in the group .

Debtor	         Name		DDDATE		Amount
Closed CO	NBC Brands Ltd	21/03/2012	 3,003 
Closed CO	ABC Ltd		17/09/2012	 1,000 
Open Co		NBC Brands Ltd	1/09/2012	 300 
Open Co		ABC Ltd		15/06/2012	 200

I am after a result below BUT (even though dates are in date format in the source data)
all the dates, even on the detail lines, come through as 0 (or 0/01/1900 when the date is formatted in date format) in pivot table
Any Ideas. Thanks I am using Excel 2007

Debtor	         		      DDDATE		  Amount
[b]Closed CO                              17/09/2012          1,000[/b]	
NBC Brands Ltd	                        21/03/2012	   3,003 
ABC Ltd		                        17/09/2012         1,000

 

[SkipVought]

hi,

Your source data DATES are NOT REAL DATES!!! Hence the ZEROS!

You can verify my claim by formatting these dates as GENERAL and observing that they do not display NUMBERS.

If they were REAL DATES, then the GENERAL format would display
[tt]
40989
41169
41153
41075
[/tt]
Unless you PRE FORMAT your date column as d/m/yyyy, the entries will be interpreted as TEXT, because Bill Gates is in the US of A.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GPnew]

Yes that worked out great thanks ...I was actually converting the numbers to text when I thought I was making sure they were dates!!


Cheers

 

[SkipVought]

Understanding Dates and Times & why they seem to be so much trouble? faq68-5827.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}