# converting month numbers to month names for pivots

#### miconian

##### Well-known Member
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?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Jonmo1

##### MrExcel MVP
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

##### Well-known Member
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.
Code:
``=DATE(,A1,1)``

#### miconian

##### Well-known Member
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.

#### miconian

##### Well-known Member
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

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

Luthius

Replies
9
Views
186
Replies
0
Views
77
Replies
2
Views
79
Replies
0
Views
70
Replies
0
Views
167

1,130,346
Messages
5,641,598
Members
417,225
Latest member
LukiiMaxim96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back