changing a month by entering a number in a cell

[change a month]

I want to create a formula to allow me to enter a # into a cell that will display a corresponding months name in that cell. In doing an IF statement I have been getting a cricular error, I assume because i'm creating the formula IN the refrence cell, or Excel can't find the project or library. Either way I'm sure I'm not creating the formula correctly.
Example: I want to enter 01 for January, 02 for February and have the corresponding mont displayed in the cell I'm creating the formula in. Is this possibe if written in the same cell? Sounds pretty simple but I can't get an "IF" statement to work for me.
It's been a loooong time since I last worked with Excel so I'd appreciate any help you can give. BTW: my office is 2002 Pro.

 

[Chip Pearson]

You don't need a formula. Just format the cell with a custom
format of mmmm.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message

I want to create a formula to allow me to enter a # into a cell

that will display a corresponding months name in that cell. In
doing an IF statement I have been getting a cricular error, I
assume because i'm creating the formula IN the refrence cell, or
Excel can't find the project or library. Either way I'm sure I'm
not creating the formula correctly.

Example: I want to enter 01 for January, 02 for February and

have the corresponding mont displayed in the cell I'm creating
the formula in. Is this possibe if written in the same cell?
Sounds pretty simple but I can't get an "IF" statement to work
for me.

It's been a loooong time since I last worked with Excel so I'd

appreciate any help you can give. BTW: my office is 2002 Pro.

 

[Bob Phillips]

You can't do it with a formula, as youy cannot have the value and the
formula in the same cell, ,the value will remove the value.

In an adjacent cell, you can have

=TEXT(DATE(2004,A1,1),"mmmm")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I want to create a formula to allow me to enter a # into a cell that will

display a corresponding months name in that cell. In doing an IF statement I
have been getting a cricular error, I assume because i'm creating the
formula IN the refrence cell, or Excel can't find the project or library.
Either way I'm sure I'm not creating the formula correctly.

Example: I want to enter 01 for January, 02 for February and have the

corresponding mont displayed in the cell I'm creating the formula in. Is
this possibe if written in the same cell? Sounds pretty simple but I can't
get an "IF" statement to work for me.

It's been a loooong time since I last worked with Excel so I'd appreciate

any help you can give. BTW: my office is 2002 Pro.

 

[JE McGimpsey]

See answer in .newusers

Please do not post the same message in multiple groups. It tends to
fragment your answers and potentially wastes the time of those answering
questions that have already been answered.

For tips on using these groups effectively, see

http://cpearson.com/excel/newposte.htm

 

[Chip Pearson]

Ignore my reply. I don't know what I was thinking.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[AlfD]

Hi!

There are ways of entering a number in a cell and getting Excel t
replace that entry with something else.

First: Tools|autocorrect... but you wouldn't like every 1 you typed t
end up as Jan!

Second: VBA. A routine which accepted the value from your cell an
returned the month name to that cell such as the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> Range("A1") Then Exit Sub
Select Case Range("A1").Value
Case Is = 1
Range("A1") = "Jan"
Case Is = 2
Range("A1") = "Feb"
Case Is = 3
Range("A1") = "Mar"
Case Is = 4
Range("A1") = "Apr"
Case Is = 5
Range("A1") = "May"
Case Is = 6
Range("A1") = "Jun"
Case Is = 7
Range("A1") = "Jly"
Case Is = 8
Range("A1") = "Aug"
Case Is = 9
Range("A1") = "Sep"
Case Is = 10
Range("A1") = "Oct"
Case Is = 11
Range("A1") = "Nov"
Case Is = 12
Range("A1") = "Dec"

End Select
End Sub

There is no formula which will change the entry cell itself: as yo
found, this generates circular reference problems.

Al