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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

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:
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
=CHOOSE(A2,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
 

gingerafro

Active Member
Joined
Mar 23, 2005
Messages
448

ADVERTISEMENT

=PROPER(TEXT(A1,"mmmm"))
 

tmcfadden

Board Regular
Joined
Aug 17, 2005
Messages
151
=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>
 

Mister H

Well-known Member
Joined
Mar 6, 2002
Messages
1,507
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
 

Forum statistics

Threads
1,136,349
Messages
5,675,243
Members
419,556
Latest member
rdecker12

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