PivotTables - Group Dates, excluding dates

[Guest]

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.

 

[Guest]

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

 

[Roger Govier]

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