Problem Converting date to month in text for use in pivot table
 

Hi,

I have a list with time entries

Date Hours worked Month
2008-10-01 4
2008-10-15 6
2008-11-01 3

I would like to add a column to the right with month in text such as oct,
nov in the example above. I have done that with formatting the date column
with "mmm", but when using that data as column data in a pivot-table, oct
appears twice (which is not surprising since it is actually different dates
in the cells even though it only shows month). So, what I think I need is
oct, nov and so on in text format in my month column so that it works in
pivot-tables.

How do I do that?

Thanks in advance,
Peter S

Problem Converting date to month in text for use in pivot table
 

Hi,

Suppose your date start in A2 then the formula for your text month is
=TEXT(A2,"MMM")

As an aside, you could group the Date field by month in the pivot table so
there would be no need for a dummy text field. Put your cursor in the date
field of the pivot table and choose PivotTable, Group and Show Detail, Group.
Select Months if necessary and click OK.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Peters" wrote:

Hi,

I have a list with time entries

Date Hours worked Month
2008-10-01 4
2008-10-15 6
2008-11-01 3

I would like to add a column to the right with month in text such as oct,
nov in the example above. I have done that with formatting the date column
with "mmm", but when using that data as column data in a pivot-table, oct
appears twice (which is not surprising since it is actually different dates
in the cells even though it only shows month). So, what I think I need is
oct, nov and so on in text format in my month column so that it works in
pivot-tables.

How do I do that?

Thanks in advance,
Peter S

Problem Converting date to month in text for use in pivot tabl
 

Dear Shane,

I went straight for the second solution, which of course was a lot smarter
than my dummy column. It worked perfectly!

Many thanks for a quick reply!

Peter

"ShaneDevenshire" wrote:

Hi,

Suppose your date start in A2 then the formula for your text month is
=TEXT(A2,"MMM")

As an aside, you could group the Date field by month in the pivot table so
there would be no need for a dummy text field. Put your cursor in the date
field of the pivot table and choose PivotTable, Group and Show Detail, Group.
Select Months if necessary and click OK.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"Peters" wrote:

Hi,

I have a list with time entries

Date Hours worked Month
2008-10-01 4
2008-10-15 6
2008-11-01 3

I would like to add a column to the right with month in text such as oct,
nov in the example above. I have done that with formatting the date column
with "mmm", but when using that data as column data in a pivot-table, oct
appears twice (which is not surprising since it is actually different dates
in the cells even though it only shows month). So, what I think I need is
oct, nov and so on in text format in my month column so that it works in
pivot-tables.

How do I do that?

Thanks in advance,
Peter S