Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table - Count only within date range
Hi,
I want the Pivot to give me separate counts for all records from dates 01/01/02 - 31/10/06 and 1/11/06-31/12/20. (Basically tell me how many records have a date that has passed since the end of October and how many are still outstanding). There must be a simple way to do this but when the field is in the ROW or COLUMN heading it will let me group and show data during a specific date range but only once - I cannot get the fields to display twice, each only showing one of the above ranges. If I put the field into the DATA area, it will not let me sort at all! I have also tried a Formula Column entering this formula =COUNTIF(Expire"<31/10/06") and it says there is an error with the formula. (Expire being the field with the information I am using). I am hoping Pivot Tables allow you to do this - or perhaps it won't let you show information from two different date ranges??? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table - Count only within date range
In the workbook, type your cutoff date, 1/11/06, in a cell
Name that cell CheckDate There are instructions here for naming a range: http://www.contextures.com/xlNames01.html Then, in the pivot table's source table, add a column with the heading "Status". In the first row of that column, enter a formula that checks the record's date. For example, with the date in column A: =IF(A2<CheckDate,"Old","Active") Copy the formula down to the last row of data. Refresh the pivot table, and add that field to the column area. Add the date field to the data area, as count of Date. Clair wrote: Hi, I want the Pivot to give me separate counts for all records from dates 01/01/02 - 31/10/06 and 1/11/06-31/12/20. (Basically tell me how many records have a date that has passed since the end of October and how many are still outstanding). There must be a simple way to do this but when the field is in the ROW or COLUMN heading it will let me group and show data during a specific date range but only once - I cannot get the fields to display twice, each only showing one of the above ranges. If I put the field into the DATA area, it will not let me sort at all! I have also tried a Formula Column entering this formula =COUNTIF(Expire"<31/10/06") and it says there is an error with the formula. (Expire being the field with the information I am using). I am hoping Pivot Tables allow you to do this - or perhaps it won't let you show information from two different date ranges??? Thanks! -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I create a schedule from a list of dates ? | Charts and Charting in Excel | |||
Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Charts and Charting in Excel | |||
Pivot Table - Multiple consolidation Range | Excel Worksheet Functions |