i want to create a formula to change a months name

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.

You can either have a value or a function in a cell, not both. When you
enter a value in the cell, the formula is overwritten.

You can use an event macro to change the value on entry. Put this in the
worksheet code module (right click on the worksheet tab and choose view
code):

Private Sub Worksheet_Change(ByVal rCell As Excel.Range)
With rCell
If .Count 1 Then Exit Sub
If .Address(False, False) = "A1" Then _
If IsNumeric(.Value) Then _
.Value = Format(DateSerial(2004, .Value, 1), "mmmm")
End With
End Sub

Change A1 to your desired cell.

In article ,
"change a month" wrote:

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.

As far as I know you can't get Excel to return the month with just a 2 digit
entry - how is Excel to know that you ment month 02 and not day 02? If you
custom format the cell as "mmmm" (without the quotes), and enter 01/01 for
January, 01/02 for February etc then you will get the month displayed.
Incidentally XL will assume the year is the current year and the day part of
the date can be any legal date.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"change a month" wrote in 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.

This should allow you to type in a number 1 - 12, anything
else will show up as entered. Use of dateserial, I think
would be slower and the example had no provision for errors.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
x = Target.Formula
Application.EnableEvents = False 'should be part of Change macro
On Error Resume Next
Target.Value = Choose(x, "January", "February", "March", _
"April", "May", "June", "July", "August", "September", _
"October", "November", "December")
done:
If IsEmpty(Target.Value) Then Target.Formula = x
Application.EnableEvents = True 'should be part of Change macro
Exit Sub
End Sub

To install right click on the sheet tab, view code, insert your coding

More on Event macros in
http://www.mvps.org/dmcritchie/excel/event.htm
watch out for the following problem, when working with
change macros that terminate.
http://www.mvps.org/dmcritchie/excel/event.htm#problems

You'll probably want to change that the "Jan", "Feb", ...
but this was what you asked for.

I think you would get more out of newsgroups if you used your
first and last name, both more friendly and more professional.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"change a month" wrote ..
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.
Example: I want to enter 01 for January, 02 for February and
have the corresponding month displayed in the cell I'm creating the formula in.

Ripped out unwanted coding and threw out the
the check for the column (column 1 is column A),
this should work better.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
if target.column 1 then exit sub
x = Target.Formula
Application.EnableEvents = False 'should be part of Change macro
On Error Resume Next
Target.Value = Choose(x, "January", "February", "March", _
"April", "May", "June", "July", "August", "September", _
"October", "November", "December")
done:
If IsEmpty(Target.Value) Then Target.Formula = x
Application.EnableEvents = True 'should be part of Change macro
Exit Sub
End Sub
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David McRitchie" wrote in message ...
This should allow you to type in a number 1 - 12, anything
else will show up as entered. Use of dateserial, I think
would be slower and the example had no provision for errors.

Thanks for the "Etiquette" advice and programming, much thanks David. I'm still a little skittish giving all that information out but maybe a decent, "User Name" would look and feel better

JE, thank you for the help, it did work. I thought, simple mindedly so apparently, it would be a matter of assigning a cell as a date then go from there. Again maybe a little simple mindedly so. With people like you and these others willing to help "newbies" such as my self, these responses puts a good light, almost a warm and fuzzy, on the "Geek-oid" myth.
Steven

----- JE McGimpsey wrote: -----

You can either have a value or a function in a cell, not both. When you
enter a value in the cell, the formula is overwritten.

You can use an event macro to change the value on entry. Put this in the
worksheet code module (right click on the worksheet tab and choose view
code):

Private Sub Worksheet_Change(ByVal rCell As Excel.Range)
With rCell
If .Count 1 Then Exit Sub
If .Address(False, False) = "A1" Then _
If IsNumeric(.Value) Then _
.Value = Format(DateSerial(2004, .Value, 1), "mmmm")
End With
End Sub

Change A1 to your desired cell.

In article ,
"change a month" wrote:

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.

Sandy - thanks much for your input, really...thank you
Steven

----- Sandy Mann wrote: -----

As far as I know you can't get Excel to return the month with just a 2 digit
entry - how is Excel to know that you ment month 02 and not day 02? If you
custom format the cell as "mmmm" (without the quotes), and enter 01/01 for
January, 01/02 for February etc then you will get the month displayed.
Incidentally XL will assume the year is the current year and the day part of
the date can be any legal date.

HTH

Sandy

--
to e-mail direct replace @mailintor.com with @tiscali.co.uk


"change a month" wrote in 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.

And another variant,

To JE and David: While I was working on this, both your replies came in and I
must admit that I have stolen a bit from both of you.

Using DateSerial, despise a possible slowdown, may have an advantage over hard
coding the month names, in that it will return the month names in language of
the system. On the other hand, if the output is to be in a specific language,
hard coding is the way to go.

My code is not any better than yours, but since I have it I can just as well
post it.

'*****
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim monthNum As Variant
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = False Then Exit Sub
monthNum = Target.Value
If monthNum 1 Or monthNum 12 Then Exit Sub
Application.EnableEvents = False
Target.Value = Format(DateSerial(1, monthNum, 1), "mmmm")
Application.EnableEvents = True
End Sub
'*****

Regards
Anders Silven

"change a month" skrev i meddelandet
...
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.

This is another "improvement" on my code, checking the
IsNumeric from John's code.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsNumeric(Target.Value) Then Exit Sub
If Target.Value 1 Or Target.Value 12 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Target.Value = Choose(Target.Value, "January", "February", _
"March", "April", "May", "June", "July", "August", _
"September", "October", "November", "December")
Application.EnableEvents = True 'should be part of Change macro
End Sub

I can find my postings on my email address which makes things
easy. My name is unique enough that I can even search on my
name to find my previous postings and replies.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Steven" wrote in message ...
Thanks for the "Etiquette" advice and programming, much thanks David. I'm still a little skittish giving all that information out
but maybe a decent, "User Name" would look and feel better