There is, but apparently you want to ignore it.

--
Regards,
Tom Ogilvy

"booshi" wrote in message
...

booshi wrote:
I have hundreds rows of data with different record dates and want to
summarise data by month/year.
But the pivot table returns records by day/month/year.


Pls help.


booshi,
The way I do it is to add another date column in the source data. This
column has a date with the same month and year as the date in the date
column but the day = 1 (i.e. the first day of the month). e.g.

Date Month StdMonth
07/09/2004 01/09/2004 01/09/3000
18/09/2004 01/09/2004 01/09/3000
03/10/2004 01/10/2004 01/10/3000
16/10/2003 01/10/2003 01/10/3000

you can then group on the Month column (format it as mmm-yy). Because
the Month column contains a date you'll find that it also sorts
correctly.

The Excel formula you would use to create the month date where the date
is in cell A3 would be:

=DATE(YEAR(A3),MONTH(A3),1)

although I actually include the month in the source data I extract from
my database so that I don't need to muck around with formulas in Excel.

As you can see I also include a 'StdMonth' column where the year is
always 3000 regardless of the actual year that I format as mmm. This
allows me to create a cross-tab table that compares monthly data from
different years. Eg.

StdMonth
Year Jan Feb Mar Apr Etc.
2001 1 5
2002 3 2
2003 2 3

Once again, because the StdMonth column is a date it sorts correctly.
The stuff I do is actually a little more complex as I report data by
financial year (which for Australian public hospitals is Jul - Jun) so,
to ensure that the months sort in financial period order, my StdMonths
a
01/07/3000
..
01/12/3000
01/01/3001
..
01/06/3001

It took me a lot banging my head against the wall to work this stuff out
so I hope it helps.

cheers
peterDavey
Austin Health
Melbourne

-----------------------------------
Thanks Peter,

I created another column =TEXT(C2, "yyyy-mm")

but I was hoping that there should be something more precise in pivot
table.

boo