Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
Entering dates in Excel 2002 | Excel Discussion (Misc queries) | |||
I get wrong dates when i paste from a different sheet into a new s | Excel Discussion (Misc queries) | |||
Filtering with dates | Excel Discussion (Misc queries) | |||
PivotTable Groupind | Excel Discussion (Misc queries) |