#1   Report Post  
JayBee
 
Posts: n/a
Default Pivottable Dates

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!
  #3   Report Post  
JayBee
 
Posts: n/a
Default

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?

"Nick Hodge" wrote:

Jaybee

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

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
HIS


"JayBee" wrote in message
...
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!




  #4   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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 he

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

JayBee wrote:
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?

"Nick Hodge" wrote:


Jaybee

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

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



"JayBee" wrote in message
...

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!






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #5   Report Post  
JayBee
 
Posts: n/a
Default

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" wrote:

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 he

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

JayBee wrote:
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?

"Nick Hodge" wrote:


Jaybee

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

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



"JayBee" wrote in message
...

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!





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




  #6   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

JayBee wrote:
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" wrote:


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 he

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

JayBee wrote:

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?

"Nick Hodge" wrote:



Jaybee

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

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



"JayBee" wrote in message
...


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!




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formating Dates for production schedule dpl7579 Excel Discussion (Misc queries) 1 January 11th 05 09:43 PM
Entering dates in Excel 2002 DickG Excel Discussion (Misc queries) 3 January 9th 05 03:56 PM
I get wrong dates when i paste from a different sheet into a new s mmollat Excel Discussion (Misc queries) 2 January 6th 05 08:35 PM
Filtering with dates T Excel Discussion (Misc queries) 1 December 29th 04 08:01 PM
PivotTable Groupind tjtjjtjt Excel Discussion (Misc queries) 0 December 2nd 04 03:37 AM


All times are GMT +1. The time now is 11:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"