View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_] Ashish Mathur[_2_] is offline
external usenet poster
 
Posts: 1,766
Default Pivot table: How do I make a monthly breakdown of daily data?

Hi,

There could be 2 reasons for this:

1. Atleast one row or column in your source data for the pivot is a blank;
or
2. Atleast one entry in the date column sof yoru pivot is a non date.

To correct for problem 2, go the source data and apply a custom filter
(select equals *) on the date column. This will show you all the non date
entries. Convert them to dates. Now get back to the pivot, refresh and now
try to group. IF it stiill does not group, then pull the date column off
the pivot, put in back in the pivot, refresh the pivot and now group

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Metro" wrote in message
...
I do have Office 07. When I right-click on one date in the pivot and
select
Group, I get the error: "Cannot group marked area". When I select several
dates and then right click and Group, it groups everything without
questions
and no monthly split.
Dates are formatted as date both in the data area as well as in the pivot.

I don't get it.

"David" wrote:

if you are using office 07- have date in date format- not text- click on
a
date in the pivot table- click group and you should automaticly get an
option
to group by month
--
HTH

"Metro" wrote:

I have my data organized as date in column A, amount in column B and
category
in column C, e.g. 3. Aug 2009 spent $ 200 on clothing and 4. Aug 2009
spent
$50 on restaurant.
I would like to keep track of montly spending on each category and the
total
of each month.
My problem is that the date dimension doesn't seem to be possible to
group
to a higher level than on the actual date from the data level.
How do I do it?