I have a data set that has a column with date of service in the format of
mm/dd/yy. I would like to be able to pivot my data set based on mm/yy. Is
there a way I can convert from one format to other without manually inputting
the new column. I know I can reformat my column to appear as I wish, however
the pivot fuction still reads each day as its own data set.

Ex:

I have...12/06/06
I want...Dec-06
and pivot to show Dec-06, Jan-07, Feb-07, etc. for the rest of the rows as
appropriate.

In an adjacent column use:

=TEXT(A1,"mmm-yy") and Pivot on that column (days are avoided)
--
Gary''s Student - gsnu200750


"Richard" wrote:

I have a data set that has a column with date of service in the format of
mm/dd/yy. I would like to be able to pivot my data set based on mm/yy. Is
there a way I can convert from one format to other without manually inputting
the new column. I know I can reformat my column to appear as I wish, however
the pivot fuction still reads each day as its own data set.

Ex:

I have...12/06/06
I want...Dec-06
and pivot to show Dec-06, Jan-07, Feb-07, etc. for the rest of the rows as
appropriate.

You can group your dates by Month and year in the pivottable--but that field has
to have dates in each row--no empty cells, no text.

Rightclick on the field in the PT. Select Group and Show Detail, then Group.

Richard wrote:

I have a data set that has a column with date of service in the format of
mm/dd/yy. I would like to be able to pivot my data set based on mm/yy. Is
there a way I can convert from one format to other without manually inputting
the new column. I know I can reformat my column to appear as I wish, however
the pivot fuction still reads each day as its own data set.

Ex:

I have...12/06/06
I want...Dec-06
and pivot to show Dec-06, Jan-07, Feb-07, etc. for the rest of the rows as
appropriate.

--

Dave Peterson