Date in Pivot table

[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.

 

[nath]

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.

 

[Tom Ogilvy]

You can also right click on the date column (in the pivot Table) and choose
group, then group on year and month.

 

[Peter Davey]

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 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]

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

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

 

[Tom Ogilvy]

There is, but apparently you want to ignore it.