Formatting Pivot Table Cells

[JamesKM]

I have just reviewed the 63 postings relating to "Pivot Table Cell Format" (search for all words) and they do not help with my problem:

I have a pivot table that simply will not show the formats selected. Eg dates show in cell B2 as 37495 (or whatever), although in the "=" field above the sheet (formula bar?) it displays 27/08/2002 which is what I want. The cells themselves have been set to specific formats, "Preserve Formatting" is ticked and yet currency and dates are not displayed as currency and dates.

I do not believe myself to be an Excel (or even Pivot Table) novice, but cannot get this seeminly simple thing to work.

Thanks in advance for any help you can give.

Also, should one thank the answerer on the posting or does that just add to the size of the site unneccesarily and it is taken as read that replies are appreciated?

 

[gvanbee]

You said that you formated the cells, but have you tried formatting the field (right click on the table>>field settings>>number button.

to answer your other question- I think it is nice to post a thank you letting the posters know that you got the information, and it was helpful, BUT only after you have verified that the problem has been solved. Too many people give thanks too early, then come back an post that it did not actually work.

 

[JamesKM]

I am afraid this does not work as I have already set and checked the field settings. To give more info:
The dated box is in the PAGE part of the table and the currency field is in the DATA part. (I have no columns or rows in the pivot as the necessary data displayed is minimal due to various macroes running from it.)
I have just downloaded Colo's utility and will attempt to show the pivot as soon as Excel finishes calculating a 35,000 long by 20 wide VLOOKUP - stupidly set off by me.

TIA for any insights and fast responses.

James

 

[gvanbee]

I could not repeat the problem. When I put a date into the page area, it comes through as a date, unless the cell is formated to number, but this would not explain why your cell is showing it unformated.

Try rebuilding the table on a new page making sure that the source data is properly formated as date for the entire column the data is contained in