Pivot tables not keeping date ranges in order

Hi all,

I have a spreadsheet that shows the date that quotes were created. I
want to create a pivot table that just summarizes the number of quotes
that were created in a given month-year format.

First I changed all the "created on" dates to month/1/year format
(where "1" is constant) instead of month/day/year (where "day" is the
specific day the quote was created: 1, 3, 4, 8, 15, 23, etc.). Then I
created a pivot table off that, but the pivot table doesn't keep the
months in order.

I have the "created on" field in the row field of the pivot table and
the "count of" the quotes in the data field. The pivot table puts the
"created on" field in some order I can't figure out. It has the order
as:

6/1/05
7/1/05
8/1/05
9/1/05
11/1/05
12/1/05
2/1/06
3/1/06
5/1/06
10/1/05
1/1/06
4/1/06

I want it to follow our fiscal year, so I want it to read:

6/1/05
7/1/05
8/1/05
9/1/05
10/1/05
11/1/05
12/1/05
1/1/06
2/1/06
3/1/06
4/1/06
5/1/06

I don't care if the format is 6/1/05 or Jun-05 or June 2005. So long
as it's not 6/13/05 where the specific date is captured. There are
far too many quotes for me to get any use out of that level of detail.

Suggestions?

Thanks.
Robert

Formatting the dates in the source data won't change the underlying date
that the pivot table uses. You could create another field in the data,
where you calculate the fiscal month, e.g. 200601, 200602, and use that
field in the pivot table.
Or, group the date field in the pivot table, by year and month. This
will group by calendar year though, not fiscal year.

darkwing_duck@myrealbox.com wrote:
> Hi all,
>
> I have a spreadsheet that shows the date that quotes were created. I
> want to create a pivot table that just summarizes the number of quotes
> that were created in a given month-year format.
>
> First I changed all the "created on" dates to month/1/year format
> (where "1" is constant) instead of month/day/year (where "day" is the
> specific day the quote was created: 1, 3, 4, 8, 15, 23, etc.). Then I
> created a pivot table off that, but the pivot table doesn't keep the
> months in order.
>
> I have the "created on" field in the row field of the pivot table and
> the "count of" the quotes in the data field. The pivot table puts the
> "created on" field in some order I can't figure out. It has the order
> as:
>
> 6/1/05
> 7/1/05
> 8/1/05
> 9/1/05
> 11/1/05
> 12/1/05
> 2/1/06
> 3/1/06
> 5/1/06
> 10/1/05
> 1/1/06
> 4/1/06
>
> I want it to follow our fiscal year, so I want it to read:
>
> 6/1/05
> 7/1/05
> 8/1/05
> 9/1/05
> 10/1/05
> 11/1/05
> 12/1/05
> 1/1/06
> 2/1/06
> 3/1/06
> 4/1/06
> 5/1/06
>
> I don't care if the format is 6/1/05 or Jun-05 or June 2005. So long
> as it's not 6/13/05 where the specific date is captured. There are
> far too many quotes for me to get any use out of that level of detail.
>
> Suggestions?
>
> Thanks.
> Robert


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

If they're really dates, you should be able to double click on that grey box in
the pivottable that represents the date.

Then click on the advanced button.
Choose Ascending in the Autosort options section.

======
By the way, you may have been able to keep the real dates, you could try
rightlclicking on that date field in the pivottable.

Choose Group and Show detail
Group
By months and by years



darkwing_duck@myrealbox.com wrote:
>
> Hi all,
>
> I have a spreadsheet that shows the date that quotes were created. I
> want to create a pivot table that just summarizes the number of quotes
> that were created in a given month-year format.
>
> First I changed all the "created on" dates to month/1/year format
> (where "1" is constant) instead of month/day/year (where "day" is the
> specific day the quote was created: 1, 3, 4, 8, 15, 23, etc.). Then I
> created a pivot table off that, but the pivot table doesn't keep the
> months in order.
>
> I have the "created on" field in the row field of the pivot table and
> the "count of" the quotes in the data field. The pivot table puts the
> "created on" field in some order I can't figure out. It has the order
> as:
>
> 6/1/05
> 7/1/05
> 8/1/05
> 9/1/05
> 11/1/05
> 12/1/05
> 2/1/06
> 3/1/06
> 5/1/06
> 10/1/05
> 1/1/06
> 4/1/06
>
> I want it to follow our fiscal year, so I want it to read:
>
> 6/1/05
> 7/1/05
> 8/1/05
> 9/1/05
> 10/1/05
> 11/1/05
> 12/1/05
> 1/1/06
> 2/1/06
> 3/1/06
> 4/1/06
> 5/1/06
>
> I don't care if the format is 6/1/05 or Jun-05 or June 2005. So long
> as it's not 6/13/05 where the specific date is captured. There are
> far too many quotes for me to get any use out of that level of detail.
>
> Suggestions?
>
> Thanks.
> Robert

--

Dave Peterson