dates

[greg]

Hello, I have a spreadsheet with claims against individual days. I am trying
to create a pivot table but want it to show a count of claims against the
monthly total. If I create the table at present selecting the date column
gives a count of claims against each seperate day. How do I easily group all
the months data together to give a monthly count. I have tried formatting
the column to show mmm-yy it changes it's appearance but when highlighting
the cell or creating the pivot table it still shows the old date format
dd/mmm/yy. Thank you in advance for your help.

 

[Frank Kabel]

Hi
one way: within the pivot table click on the date column and goto the
pivot table menu. Group the data column

another way: In your source data add a helper column with the formula
=TEXT(A1,"MM-YYYY")
and copy this for all rows (assumption column a stores your dates). Now
use this helper column in your pivot table

 

[Dave Peterson]

If you use Frank's first suggestion (I would), make sure you group by Year and
Month (unless you want all of January's data (2000-2004) grouped together.

If you use Frank's second suggestion, I think I'd use a slightly different
formula:

=text(a2,"yyyy-mm")

Then your sorts will be easier.