Pivottable Dates

[Guest]

I am using windows XP with Excel 2000, and I want my pivottable to display
sums of $ amounts for certain date ranges. Worksheet is formatted as Column
"G" is dollar amounts, and Column "I" is dates (formatted xx/xx/xx). For
example, I want a total sales amount for posting dates between
3/03/03-04/04/04. I realize I can use dropdowns on the pivottable and
uncheck each unwanted date, but is there a calculated field I can create to
do this automatically. (I tried IF & SUMIF, with 'xx/xx/xx<(name of column
I)<xx/xx/xx', but maybe I'm formatting incorrectly.) Thanks for any help!

 

[Nick Hodge]

Jaybee

Right-click on you date header and select group and show detail>group...

In the dialog, set the date range you want and select days and type 1 in the
'number of days' box

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Thanks. But when I try Group & Outline > Group, I get the dreaded "cannot
group that selection" exclamation. I think I've tried reformatting the date
field a dozen ways to try to be able to group, but I always get that message.
Do you know if this is a formatting issue or something else?

 

[Debra Dalgleish]

Usually when you get that message it's because there are blank cells in
the date column, or some of the dates aren't being recognized as real
dates. There are some suggested fixes here:

http://www.contextures.com/xlPivot07.html#Problems

 

[Guest]

You solved it! It was because of blank cells. Now the issue is that I
usually have blank cells until orders are finalized, so do I create a phony
date in those cells just to keep them from being blank? Thanks!

 

[Debra Dalgleish]

Perhaps you could enter a date a year or two in the future. That would
distinguish them from finalized orders.