Retreiving cell 'formatted' value

[yumbelie]

Hiya, I'm wondering if their is any way to retrieve the cells formatted value, rather then the actual raw data, to explain - I have a pivot table, and a nice large dataset - now the cells in one of the columns in the data set are all dates, but have been formatted to Month-Year (Febuary-2002, January-2002 etc). Problem is, when I build a pivot, it uses the underlying data, which I don't want as I need to summerise by month. What I wanted to do was a quick macro to step through all the records and replace the actual data with the formatted data value, then my pivot will respond correctly . Thanks guys.

Joseph.

 

[combo]

A date is always a number, whatever you format it. A pivot table will use it in the report.
So you can either use grouping option of pivot table, and select month and year (nb., groupping is impossible if there are empty fields), or add another column to the table with Month + Year and use it instead of data.

 

[yumbelie]

Good idea Dunno why I didn't think of that, these early mornings are really stunting my brains growth