Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTables - Group Dates, excluding dates
Wow, this discussion group is awesome!
I use PivotTables to show data by month. Often, I take data out of SQL datasources, and use only data that is relevant for the pivot table. For example, I may want to show the last twelve months totals for a particular category of sales. To do this, I use a filter so the PivotTable only grabs data where the dates are in a range over the last twelve months -- this eliminates the problem of "Grouping" dates where there may be an absense of the date (Excel apparently won't let you group dates if one date is blank) My problem: My IT guys connected me to a new datasource (data source type is acronym and is not coming to me now, but is 4 letters, and begins with "M). This datasource is an ACT! database. It does not let me filter out the "Empty" dates, so when I go to group the data by month, Excel tells me I can't group it -- it doesn't like the fact that at least one of the date fields in the database is "blank". Suggestions? If I'm unclear, let me know! I would forever be indebted to you if you have this answer. |
#2
|
|||
|
|||
I can't duplicate the problem as I am able to group when the date is left blank in certain records. Check to make sure all columns have a name, ie. you have not blank columns. I know PT don't like entire blank columns of data.
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTables - Group Dates, excluding dates
I can send you a sample file, if it helps.
I believe blank date fields prevents you from grouping the dates in that column. The only non-technical solution, if this is true, would be to put dummy data in all remaining blank fields...maybe default to 1/1/1910 "cs02000" wrote: I can't duplicate the problem as I am able to group when the date is left blank in certain records. Check to make sure all columns have a name, ie. you have not blank columns. I know PT don't like entire blank columns of data. -- cs02000 |
#4
|
|||
|
|||
Send it to "
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
PivotTables - Group Dates, excluding dates
Hi Todd
Could you Sort the database by Date. Then all the rows with blank dates would fall to the bottom. Base your PT on the rows containing dates. Regards Roger Govier Todd1 wrote: I can send you a sample file, if it helps. I believe blank date fields prevents you from grouping the dates in that column. The only non-technical solution, if this is true, would be to put dummy data in all remaining blank fields...maybe default to 1/1/1910 "cs02000" wrote: I can't duplicate the problem as I am able to group when the date is left blank in certain records. Check to make sure all columns have a name, ie. you have not blank columns. I know PT don't like entire blank columns of data. -- cs02000 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why aren't dates always available to group in pivot tables? | Charts and Charting in Excel | |||
Time between two dates (Excluding Non-bus Hrs) | Excel Worksheet Functions | |||
Can't group by dates in Excel 2003 pivot table | Excel Discussion (Misc queries) | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |