converting month numbers to month names for pivots

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
768
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...
 

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
Joined
Oct 12, 2006
Messages
44,061
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
Joined
Jul 26, 2004
Messages
522
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
Joined
Aug 18, 2004
Messages
768
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
Joined
Aug 18, 2004
Messages
768
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
Joined
Apr 5, 2011
Messages
323
Follow the step by step from the post and you'll convert dates in months as showed there.

Luthius
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top