Pivot Table - Grouped Dates - Format

Hi All,

I have a grouped pivot by date. Works fine except for the format of
the date doesn't allow sorting in the way that I need.

The data has the dates in the format: 4/17/2006 14:21

After grouping in the pivot on that field by day (to remove the time
portion) I get: 10-Apr


Sorting the pivot gives:

10-Apr
10-Mar
11-Apr
12-Apr
13-Apr
13-Mar
14-Apr
14-Mar
15-Apr
15-Mar
16-Mar
17-Apr


Rather than by year/month/day. I tried changing the field format in
the pivot and it doesn't do anything at all to how the format in the
pivot looks? I need the pivot data to sort as actual days are in
calendar order. In my example data, it would have all March data
followed by April data rather than the tenth day of either month
followed by the eleventh day of either month etc...

Any ideas? Thanks for any help in advance.

Hi,
what I do to avoid problems with Date is that I split date in separeta cell
(Day, Month, Year) - then I have full control of Date.
jirkalla

"PlanoMax" wrote:

>
> Hi All,
>
> I have a grouped pivot by date. Works fine except for the format of
> the date doesn't allow sorting in the way that I need.
>
> The data has the dates in the format: 4/17/2006 14:21
>
> After grouping in the pivot on that field by day (to remove the time
> portion) I get: 10-Apr
>
>
> Sorting the pivot gives:
>
> 10-Apr
> 10-Mar
> 11-Apr
> 12-Apr
> 13-Apr
> 13-Mar
> 14-Apr
> 14-Mar
> 15-Apr
> 15-Mar
> 16-Mar
> 17-Apr
>
>
> Rather than by year/month/day. I tried changing the field format in
> the pivot and it doesn't do anything at all to how the format in the
> pivot looks? I need the pivot data to sort as actual days are in
> calendar order. In my example data, it would have all March data
> followed by April data rather than the tenth day of either month
> followed by the eleventh day of either month etc...
>
> Any ideas? Thanks for any help in advance.
>
>

Thanks for the reply. It is a automated web retreival and the pivots
are updated via macros. The excel workbook is used by many as well.

Was hoping there was a way to format the grouped pivot date. Anyone?

Immediately after you group the dates, they should be in chronological
order. If you sort them later, they'll sort in text order.

You could regroup them, and they should go back to date order.

PlanoMax wrote:
> Hi All,
>
> I have a grouped pivot by date. Works fine except for the format of
> the date doesn't allow sorting in the way that I need.
>
> The data has the dates in the format: 4/17/2006 14:21
>
> After grouping in the pivot on that field by day (to remove the time
> portion) I get: 10-Apr
>
>
> Sorting the pivot gives:
>
> 10-Apr
> 10-Mar
> 11-Apr
> 12-Apr
> 13-Apr
> 13-Mar
> 14-Apr
> 14-Mar
> 15-Apr
> 15-Mar
> 16-Mar
> 17-Apr
>
>
> Rather than by year/month/day. I tried changing the field format in
> the pivot and it doesn't do anything at all to how the format in the
> pivot looks? I need the pivot data to sort as actual days are in
> calendar order. In my example data, it would have all March data
> followed by April data rather than the tenth day of either month
> followed by the eleventh day of either month etc...
>
> Any ideas? Thanks for any help in advance.
>