converting month numbers to month names for pivots

[miconian]

This seems to be the oldest Excel question in history. And yet, having read various pages around the web about how to solve it, I still haven't found a solution that I've been able to implement successfully.

My data sheet has a Month column, and those months are simply numbers one to twelve. They are formatted as plain old numbers. The data is coming from somewhere outside Excel.

How the numbers look in the data sheet doesn't matter. But I use this data to generate pivots, wherein the months are the columns. I want it to say Jan, Feb, etc. across the top of the pivot.

I realize that I could just loop through the raw data and convert the numbers to text. However, I want the pivot table to recognize this data as dates, so that I can do date-related stuff, such as the date grouping described on p. 85 of Jelen's Pivot Table Data Crunching book.

If I go into the raw data and try to convert the cells to the mmm format, they all convert to January.

I'm aware that formatting doesn't translate from raw data to pivot table anyhow. But it's not clear to me how I can use the date field grouping functions without somehow showing Excel that these are dates. Or is the pivot table smart enough to realize that just by the names, even if they are formatted as text?

thanks for your help...

 

[Jonmo1]

You can convert them into actual dates, say the 1st day of given month in current year..

=DATE(YEAR(TODAY()),A1,1)

Where A1 is your month number.

Hope that helps.

 

[Phox]

If you use the Date formula, you can format the cell using mmm and it will still be used as a date. The reason your current numbers all appear as January is that the date is translated as 1 = Jan 1, 2 = Jan 2, etc.

=DATE(,A1,1)

 

[miconian]

Thanks guys, those suggestions both seem to work.

If anyone feels like commenting on a good approach to writing some VBA code that will add that column to the table in question, I would read it avidly.

 

[Luthius]

Check this:
http://www.mrexcel.com/articles/pivot-table-group-dates-by-month.php

Luthius

 

[miconian]

Thanks, Lithius. Actually, I am already using the grouping method described in that post... now that I have the numbers converted to months. Although the same post says that "there is an easier way" to convert the numbers to months, it doesn't say what it is, and as far as I can tell, the grouping function does not do this by itself. Maybe I'm mis-reading.

 

[Luthius]

Follow the step by step from the post and you'll convert dates in months as showed there.

Luthius