Pivot Table Summarizing Quirk - Please Help

[Brad Autry]

I am using Excel 2007.

I have a spreadsheet that has about 20 columns of data for each day of the
week in a calendar year. I create a pivot table that summarizes this data,
broken down by Month, and then by Week.

There is no data for Saturdays and Sundays, but the empty rows must remain
in the source data.

Now, my problem:

When I create the pivot table, I summarize data by month with Average, MAX,
MIN, and Sums.

Because there are blanks in the data, the MAX and MIN functions are only
looking at the final week of the month. Why is this? How can I get around
it? Entering 00:00:00 is not an option, as the MIN data would error.

To replicate the problem if you are so inclined, you could just make a
spreadsheet with just two columns, like this:

1/1/2010 0:08:54
1/2/2010
1/3/2010
1/4/2010 0:11:54
1/5/2010 0:11:29
1/6/2010 0:06:59
1/7/2010 0:31:54
1/8/2010 0:26:41
1/9/2010
1/10/2010
1/11/2010 0:03:20
1/12/2010 0:02:24
1/13/2010 0:15:26
1/14/2010 0:08:48
1/15/2010 0:03:02
1/16/2010
1/17/2010
1/18/2010 0:24:44
1/19/2010 0:01:42
1/20/2010 0:12:01
1/21/2010 0:01:14
1/22/2010 0:17:21
1/23/2010
1/24/2010
1/25/2010 0:20:00
1/26/2010 0:06:02
1/27/2010 0:23:48
1/28/2010 0:10:01
1/29/2010 0:03:03
1/30/2010
1/31/2010

Date as the Row Label. Max of Delay as the Values. The MAX value in the
overall summary shows 00:23:48.

Please help me!

Many thanks in advance,
Brad

 

[Brad Autry]

As a follow-up to my own post here, I'd just like to point out that the SUM
and Average summary figures take into account the whole month. Only the MAX
and MIN values do not show properly, as they only take into account the final
week.

If I were to enter data into the Sat and Sun blanks, then MAX and MIN work
properly for the month. But what values could I enter that would not mess up
the other figures? There are none that I can think of.

Bleh.