Formatting a cell to Display MONTH ONLY

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
Hi All:

Hopefully this is an easy one. I am trying to format a cell so that it will display the month (Full Name) after the person gets out of the cell.

I want the user to enter 1 to 12 and then have the Alpha of that month displayed whern they exit the cell. I can get it to Display the month but ionly if I type the year as well.

If the user type a 6 or 06 I want that cell to display June when they exit it.

Any suggestions?

THANKS,
Mark
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
That won't work for Mark.

That would only work if the user entered a full date.

I'm sure there's another way to do it though. Maybe a macro that fires off on an OnChange event over the certain range of cells.

-Tim
 
Upvote 0
That won't work for Mark.

That would only work if the user entered a full date.

I'm sure there's another way to do it though. Maybe a macro that fires off on an OnChange event over the certain range of cells.

-Tim

You're right.....I screwed that one up. :oops:
 
Upvote 0
=CHOOSE(A2,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
 
Upvote 0
=CHOOSE(A2,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Won't that only work if the number is in a different cell than the formula?

Mark, put this in the worksheet that you want this functionality. You'll have to change the "rngChangeToMonth" range as needed.

-Tim

<hr><font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)

<SPAN style="color:#00007F">Dim</SPAN> rngChangeToMonth <SPAN style="color:#00007F">As</SPAN> Range, Isect
<SPAN style="color:#00007F">Set</SPAN> rngChangeToMonth = Sheet1.Range("A1,A5")

<SPAN style="color:#00007F">If</SPAN> (Target.Cells.Count = 1) <SPAN style="color:#00007F">Then</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> Isect = Application.Intersect(Target, rngChangeToMonth)
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> (Isect Is <SPAN style="color:#00007F">Nothing</SPAN>) <SPAN style="color:#00007F">Then</SPAN>
        Target = Format(Target.Value & "/1/2006", "mmmm")
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT><hr>
 
Upvote 0
Hi:

THANKS for the replys... You are correct in that the Normal FOrmatting will not work.

West Man's will work but the only problem with that is that from what I can tell I would have to use 2 cells to make it work. One to enter the 6 in and another to display June. I can do that but was hoping for a one cell solution. If I can't find that using formatting then I guess I will have to use the 2 cell method. Anyone else have a suggestion.

West Man: If I am misunderstanding your solution let me know. THANKS.

Bye 4 Now,
Mark
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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
Back
Top