Date in Pivot table

  • Thread starter Thread starter booshi
  • Start date Start date
B

booshi

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.
 
You could put in a few hidden columns next to the dates,
using

=month(date cell)
=year(date cell)

this will show for example

=month(c1), with c1 being "01/05/04", will show a result
of 5. You can then have these in your pivot table
selection. You could even run a vlookup using the month
result against it's actual month name

1 January
2 February

=vlookup(month(date cell),(lookup table range),2,0)

Hope this helps

Nath.
 
You can also right click on the date column (in the pivot Table) and choose
group, then group on year and month.
 
booshi said:
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.
Click to expand...
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 are:
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
 
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 are:
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
Click to expand...

-----------------------------------
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
 
Back
Top