Pivot Table sorts date as Alpha, not as Date

[Jimbo213]

To level-set: I am an experienced excel + pivot table user, not a beginner.
Something strange is happening to my pivot table that I've not seen before.

I have created one pivot table and "date reported" is the row field of the
pivot table. The input data is mm/dd/yyyy hh:mm:ss AM and formatted
mm/dd/yyyy.
I am using other page and column fields like a normal pivot table.
Everything is fine ... date sorts correct as expected. Graph looks great.

I then right-clicked the tab and copied to a new tab ... to make a different
underlying pivot table so as to create a different graph

I did this about 10 times. 1 data tab, 10 pivot tables, 10 charts.
[Yes - the spreadsheet is huge: now 30 mb]

By the way, the date-reported field is grouped differently on the 10 pivot
tables.
[year-month; days-7; days-14 ... each pivot table is different]

Now the date-reported on some of the pivot tables is sorting as if it was a
text field. ex: 01/02/07, 01/03/06. Strangely, some pivot tables have
changed to a different date format. ex: 14-Mar, 21,-Mar.

I have tried to change the format and it won't budge.
I have tried to sort manual, ascending, descending ... it won't budge.

Is my spreadsheet just too big?

Any ideas?

 

[Barb Reinhardt]

I wonder if you have some data in your source that's not numeric and is being
seen as text.

Barb Reinhardt

 

[Debra Dalgleish]

If all the pivot tables are copies of the original pivot table, they're
based on the same pivot cache. When you group the date field in one
pivot table, the same grouping is applied to all the pivot tables that
use the same pivot cache.

To make them work independently, you can base each pivot table on the
source data, instead of the original pivot table. Of course, that will
make your file even larger.

Or, instead of grouping, add YearMonth, WeekStart7, WeekStart14 columns
to the source data, and use those fields in the row area of the pivot table.

You may prefer to record macros as you change the grouping and layout.
Then, create only one pivot table, and run the macros as required.

To level-set: I am an experienced excel + pivot table user, not a beginner.
Something strange is happening to my pivot table that I've not seen before.

I have created one pivot table and "date reported" is the row field of the
pivot table. The input data is mm/dd/yyyy hh:mm:ss AM and formatted
mm/dd/yyyy.
I am using other page and column fields like a normal pivot table.
Everything is fine ... date sorts correct as expected. Graph looks great.

I then right-clicked the tab and copied to a new tab ... to make a different
underlying pivot table so as to create a different graph

I did this about 10 times. 1 data tab, 10 pivot tables, 10 charts.
[Yes - the spreadsheet is huge: now 30 mb]

By the way, the date-reported field is grouped differently on the 10 pivot
tables.
[year-month; days-7; days-14 ... each pivot table is different]

Now the date-reported on some of the pivot tables is sorting as if it was a
text field. ex: 01/02/07, 01/03/06. Strangely, some pivot tables have
changed to a different date format. ex: 14-Mar, 21,-Mar.

I have tried to change the format and it won't budge.
I have tried to sort manual, ascending, descending ... it won't budge.

Is my spreadsheet just too big?

Any ideas?

 

[Jimbo213]

Good idea but not the issue. Raw Data is exported from a Lotus Notes view.
Notes controls the format on the field as MM/DD/YYYY HH:MM:SS AM on every
value.
--
Thanks for your reply & assistance. Please try again.
Jimbo213

I wonder if you have some data in your source that's not numeric and is being
seen as text.

Barb Reinhardt

To level-set: I am an experienced excel + pivot table user, not a beginner.
Something strange is happening to my pivot table that I've not seen before.

I have created one pivot table and "date reported" is the row field of the
pivot table. The input data is mm/dd/yyyy hh:mm:ss AM and formatted
mm/dd/yyyy.
I am using other page and column fields like a normal pivot table.
Everything is fine ... date sorts correct as expected. Graph looks great.

I then right-clicked the tab and copied to a new tab ... to make a different
underlying pivot table so as to create a different graph

I did this about 10 times. 1 data tab, 10 pivot tables, 10 charts.
[Yes - the spreadsheet is huge: now 30 mb]

By the way, the date-reported field is grouped differently on the 10 pivot
tables.
[year-month; days-7; days-14 ... each pivot table is different]

Now the date-reported on some of the pivot tables is sorting as if it was a
text field. ex: 01/02/07, 01/03/06. Strangely, some pivot tables have
changed to a different date format. ex: 14-Mar, 21,-Mar.

I have tried to change the format and it won't budge.
I have tried to sort manual, ascending, descending ... it won't budge.

Is my spreadsheet just too big?

Any ideas?

 

[Peo Sjoblom]

It can still be seen as text, one of the most common problems in Excel is
import from other programs seen as text, what happens if you use a formula
like

=ISTEXT(A2)

where A2 is one of the offending cells, if it returns TRUE it is seen as
text.
You could format an empty cell and date, copy it and select an offending
cell and do edit>paste special and select add, that might convert the cell
to number format


--
Regards,

Peo Sjoblom

Good idea but not the issue. Raw Data is exported from a Lotus Notes
view.
Notes controls the format on the field as MM/DD/YYYY HH:MM:SS AM on every
value.
--
Thanks for your reply & assistance. Please try again.
Jimbo213

I wonder if you have some data in your source that's not numeric and is
being
seen as text.

Barb Reinhardt

To level-set: I am an experienced excel + pivot table user, not a
beginner.
Something strange is happening to my pivot table that I've not seen
before.

I have created one pivot table and "date reported" is the row field of
the
pivot table. The input data is mm/dd/yyyy hh:mm:ss AM and formatted
mm/dd/yyyy.
I am using other page and column fields like a normal pivot table.
Everything is fine ... date sorts correct as expected. Graph looks
great.

I then right-clicked the tab and copied to a new tab ... to make a
different
underlying pivot table so as to create a different graph

I did this about 10 times. 1 data tab, 10 pivot tables, 10 charts.
[Yes - the spreadsheet is huge: now 30 mb]

By the way, the date-reported field is grouped differently on the 10
pivot
tables.
[year-month; days-7; days-14 ... each pivot table is different]

Now the date-reported on some of the pivot tables is sorting as if it
was a
text field. ex: 01/02/07, 01/03/06. Strangely, some pivot tables have
changed to a different date format. ex: 14-Mar, 21,-Mar.

I have tried to change the format and it won't budge.
I have tried to sort manual, ascending, descending ... it won't budge.

Is my spreadsheet just too big?

Any ideas?

 

[Jimbo213]

=ISTEXT(C2) shows false for all 4785 rows.
please keep tyring.

Thanks for your reply & assistance.
Jimbo213

It can still be seen as text, one of the most common problems in Excel is
import from other programs seen as text, what happens if you use a formula
like

=ISTEXT(A2)

where A2 is one of the offending cells, if it returns TRUE it is seen as
text.
You could format an empty cell and date, copy it and select an offending
cell and do edit>paste special and select add, that might convert the cell
to number format


--
Regards,

Peo Sjoblom

Good idea but not the issue. Raw Data is exported from a Lotus Notes
view.
Notes controls the format on the field as MM/DD/YYYY HH:MM:SS AM on every
value.
--
Thanks for your reply & assistance. Please try again.
Jimbo213

I wonder if you have some data in your source that's not numeric and is
being
seen as text.

Barb Reinhardt



:


To level-set: I am an experienced excel + pivot table user, not a
beginner.
Something strange is happening to my pivot table that I've not seen
before.

I have created one pivot table and "date reported" is the row field of
the
pivot table. The input data is mm/dd/yyyy hh:mm:ss AM and formatted
mm/dd/yyyy.
I am using other page and column fields like a normal pivot table.
Everything is fine ... date sorts correct as expected. Graph looks
great.

I then right-clicked the tab and copied to a new tab ... to make a
different
underlying pivot table so as to create a different graph

I did this about 10 times. 1 data tab, 10 pivot tables, 10 charts.
[Yes - the spreadsheet is huge: now 30 mb]

By the way, the date-reported field is grouped differently on the 10
pivot
tables.
[year-month; days-7; days-14 ... each pivot table is different]

Now the date-reported on some of the pivot tables is sorting as if it
was a
text field. ex: 01/02/07, 01/03/06. Strangely, some pivot tables have
changed to a different date format. ex: 14-Mar, 21,-Mar.

I have tried to change the format and it won't budge.
I have tried to sort manual, ascending, descending ... it won't budge.

Is my spreadsheet just too big?

Any ideas?

 

[Jimbo213]

Thanks, I wasn't aware of a "pivot cache".
I'll try your first suggestion (new source data pivot table for every chart)
because I need all graphs to be available to mgt as they click thru the tabs.
File size is not a big deal because server/network is large & fast.

Thanks for your reply & assistance.
Jimbo213
------------------------

If all the pivot tables are copies of the original pivot table, they're
based on the same pivot cache. When you group the date field in one
pivot table, the same grouping is applied to all the pivot tables that
use the same pivot cache.

To make them work independently, you can base each pivot table on the
source data, instead of the original pivot table. Of course, that will
make your file even larger.

Or, instead of grouping, add YearMonth, WeekStart7, WeekStart14 columns
to the source data, and use those fields in the row area of the pivot table.

You may prefer to record macros as you change the grouping and layout.
Then, create only one pivot table, and run the macros as required.

To level-set: I am an experienced excel + pivot table user, not a beginner.
Something strange is happening to my pivot table that I've not seen before.

I have created one pivot table and "date reported" is the row field of the
pivot table. The input data is mm/dd/yyyy hh:mm:ss AM and formatted
mm/dd/yyyy.
I am using other page and column fields like a normal pivot table.
Everything is fine ... date sorts correct as expected. Graph looks great.

I then right-clicked the tab and copied to a new tab ... to make a different
underlying pivot table so as to create a different graph

I did this about 10 times. 1 data tab, 10 pivot tables, 10 charts.
[Yes - the spreadsheet is huge: now 30 mb]

By the way, the date-reported field is grouped differently on the 10 pivot
tables.
[year-month; days-7; days-14 ... each pivot table is different]

Now the date-reported on some of the pivot tables is sorting as if it was a
text field. ex: 01/02/07, 01/03/06. Strangely, some pivot tables have
changed to a different date format. ex: 14-Mar, 21,-Mar.

I have tried to change the format and it won't budge.
I have tried to sort manual, ascending, descending ... it won't budge.

Is my spreadsheet just too big?

Any ideas?