We help IT Professionals succeed at work.

Macro to change number to Month in Excel?

143 Views
Last Modified: 2017-03-09
I have a Excel spread sheet that has a column named Sold Month with a number like 1 for January or 8 for August and a similar column for Purchased month.

Is there a way to add a macro that will look at each column and rename the cell from 1 to January, 12 to December ect?
Comment
Watch Question

akb
CERTIFIED EXPERT

Commented:
Inset a column next to the column with the month numbers.
Put the following formula into the first cell of the new column
=TEXT(DATE(2000,A1,1),"mmmm")
Change A1 to the cell reference of the first date.
Copy that cell down the column to the last row.
That will give you the months.
If you want, copy the new months over the top of the month numbers by using Paste Special and paste as Values.
Then delete the column with the formulas.
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You can try MonthName VBA function to achieve this....

Try something like this....
Sub MonthNumberToMonthName()
Dim lr As Long
Dim rng As Range, cell As Range
'Assuming SoldMonth column is Column A
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("A2:A" & lr)
For Each cell In rng
    cell = MonthName(cell)
Next cell
End Sub

Open in new window

Author

Commented:
Hello, thanks for the info!

When I try "   =TEXT(DATE(2000,A1,1),"mmmm")   " I just get a #NUM! error in the cell, adjusted the column info so should work ok.  Tried on one cell and a range. The dates range through several different months and years.


When I try to run the macro I get a error on the line below. I adjusted the range info but still getting a error.
cell = MonthName(cell)
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
Can you upload a small sample workbook?

Author

Commented:
This is a test sheet of the type of data I am working with. The goal is to use a pivot table that will categorize the info by year and month but I have a large sheet I need to apply this to so I am trying to figure out a easier way of filling in the month and year cells other than manually doing it and risking making a mistake.
Test_Pivot_Table_Sales_Sheet.xlsx
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You just need to group the dates (sold date) in the pivot table with Month & Year.
Right click on any date cell in pivot table --> Choose Group and select Month & Year and click OK to finish.
You can optionally drop the Year to the columns area to see more useful report.

Refer to the attached.
Test_Pivot_Table_Sales_Sheet.xlsx

Author

Commented:
Thanks that is much easier. One question though is how did you get the Years option? I don't see that in the data field.
Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
OK got it, works great, thanks.
Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome Reiusa! Glad I could help.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.