pivot table

[Guest]

A simple one probably

I have pivot table which has dates in column field.

The date format is MMM-YY.

However when it makes the pivot table.. Its sort the data by text ( April,
August, January, July,etc)
but not in the date format ...Jan / feb/ march,,,,,

Why is taht

how to resolve

 

[Roger Govier]

Hi

Set yourself up a new list under Tools>Options>List for Jan, Feb, etc.
Then make the field sort again.

Regards

Roger Govier

 

[Debra Dalgleish]

In the source data, are these real dates, formatted as MMM-YY, or are
they text entries?

If they're real dates, you can group them in the pivot table --
right-click the date field button, choose Group and Show Detail > Group
Group by Years and Months

If it's text in the source data, you could follow Roger's suggestion to
create a custom list. You'd need to list the months in mmm-yy format, as
you want them to sort.

 

[Guest]

Hi Debra,

I tried your suggestion with a pivot table I'm having problems with and I
had new problems. One the group data only groups by month or year and not
both. Second prevailing problem is that the data in my pivot table will not
refresh with the new data I entered. I have dates in my pivot table also.
It seems like office 2003 sbe is defective.

 

[Roger Govier]

Hi Eleanor

With regard to grouping by Year and Month, they are not mutually
exclusive.
If you click on Year, you can also click on Month and both will stay
selected (as will ill Quarter, if you chose to select that as well)
The above only applies if you do have valid Excel dates in your source
data, not text values.

With regard to refreshing data, have you tried clicking the Refresh icon
on the PT toolbar. Its the Red exclamation mark.
If this doesn't include the data you have just added, then it is
probably because the range you defined for the Pivot Table does not
include the rows you have just added.
Create a Dynamic range for the Pivot Table instead.
Debra has excellent examples of how to do this at
http://www.contextures.com/xlPivot01.html