=Text(n,"mmm") always returns "Jan" when n=1..12
 

Excel 2007 - Why doesn't this work? Being forced to use VLookup and make a
month table each time I want to convert a month number to a month nme

Answer: =Text(n,"mmm") always returns "Jan" when n=1..12
 

It sounds like the TEXT function in Excel is not recognizing the month number and is defaulting to "Jan" for all values. This could be due to a formatting issue or a language setting in Excel.

Here are a few steps you can try to resolve the issue:

1. Check the formatting of the cell containing the month number. Make sure it is formatted as a number and not as text. You can do this by selecting the cell and going to the Home tab Number group Number format dropdown.
2. Check the language setting in Excel. Go to File Options Language. Make sure the language is set to the correct language for your region.
3. Try using the MONTH function instead of the TEXT function. The MONTH function returns the month number as a value between 1 and 12, which you can then use in a VLOOKUP formula to return the month name. For example, if the month number is in cell A1, you could use the formula =VLOOKUP(A1,{1,"Jan";2,"Feb";3,"Mar";4,"Apr";5,"Ma y";6,"Jun";7,"Jul";8,"Aug";9,"Sep";10,"Oct";11,"No v";12,"Dec"},2,FALSE) to return the month name.

=Text(n,"mmm") always returns "Jan" when n=1..12
 

n has to be a complete date value (yeae, month, day).
Try
=Text(date(2009,n,1),"mmm")
Regards,
Stefi

€žBlaze-Of-Glory€� ezt Ã*rta:

Excel 2007 - Why doesn't this work? Being forced to use VLookup and make a
month table each time I want to convert a month number to a month nme

=Text(n,"mmm") always returns "Jan" when n=1..12
 

Try
=TEXT(date(year(today()),n,1),"mmm")

If this post helps click Yes
---------------
Jacob Skaria


"Blaze-Of-Glory" wrote:

Excel 2007 - Why doesn't this work? Being forced to use VLookup and make a
month table each time I want to convert a month number to a month nme

=Text(n,"mmm") always returns "Jan" when n=1..12
 

n=1 is 1 Jan 1900
n=2 is 2 Jan 1900
n=3 is 3 Jan 1900
....
n=12 is 12 Jan 1900.

Still January.
--
David Biddulph

"Blaze-Of-Glory" wrote in message
...

Excel 2007 - Why doesn't this work? Being forced to use VLookup and make
a
month table each time I want to convert a month number to a month nme

=Text(n,"mmm") always returns "Jan" when n=1..12
 

Try this:

=TEXT(n*30,"mmm")

Where n = 1 to 12

--
Biff
Microsoft Excel MVP


"Blaze-Of-Glory" wrote in message
...
Excel 2007 - Why doesn't this work? Being forced to use VLookup and make
a
month table each time I want to convert a month number to a month nme

=Text(n,"mmm") always returns "Jan" when n=1..12
 

Thanks to all those who replied - It's not at all clear from the help for
text() that the date format expects epoch time. I am still missing the
Monthname() formula I used to have in previous versions of excel.

"Blaze-Of-Glory" wrote:
Excel 2007 - Why doesn't this work? Being forced to use VLookup and make a
month table each time I want to convert a month number to a month nme

=Text(n,"mmm") always returns "Jan" when n=1..12
 

To the best of my knowledge, Excel (at the worksheet level) never had a
MonthName function; however, there is one at the VBA level. Perhaps you had
UDF (user defined function) that provided this functionality. The function
would have looked like this...

Function MonthName(MonthNumber As Long) As String
MonthName = VBA.MonthName(MonthNumber)
End Function

and it would have been installed in a Module.

--
Rick (MVP - Excel)


"Blaze-Of-Glory" wrote in message
...
Thanks to all those who replied - It's not at all clear from the help for
text() that the date format expects epoch time. I am still missing the
Monthname() formula I used to have in previous versions of excel.

"Blaze-Of-Glory" wrote:
Excel 2007 - Why doesn't this work? Being forced to use VLookup and make
a
month table each time I want to convert a month number to a month nme

=Text(n,"mmm") always returns "Jan" when n=1..12
 

For those who might be interested in this method, the numbers 28, 29 and 30
can all be used as the multiplier.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Try this:

=TEXT(n*30,"mmm")

Where n = 1 to 12

--
Biff
Microsoft Excel MVP


"Blaze-Of-Glory" wrote in
message ...
Excel 2007 - Why doesn't this work? Being forced to use VLookup and make
a
month table each time I want to convert a month number to a month nme

=Text(n,"mmm") always returns "Jan" when n=1..12
 

Blaze-Of-Glory wrote...
. . .It's not at all clear from the help for
text() that the date format expects epoch time. . . .

No?

So you'd expect the TEXT function has the smarts to render TEXT
(2,"dddd") as "Monday" in countries which consider Sunday the first
day of the week but as "Tuesday" in countries which consider Monday
the first day of the week in addition to being able to render TEXT
(2,"mmmm") as "February"?

The TEXT function only provides the same number formatting as cell
number formatting via the Format Cells dialog. While on-line help for
the TEXT function doesn't spell this out explicitly, it does imply it.

. . . I am still missing the
Monthname() formula I used to have in previous versions of excel.

There's never been a built-in MONTHNAME function in any version of
Excel. What version do you believe provided it? Or are you recalling
this from a different spreadsheet program like Quattro Pro?

The MONTHNAME function you had could have been provided by an add-in.
You could still use an add-in to provide custom functions in your
current Excel version.

=Text(n,"mmm") always returns "Jan" when n=1..12
 

Here's how it works...

**Only works using the default date system.

1*30 = serial date 30 = Jan 30 1900
2*30 = serial date 60 = Feb 29 1900 (1)
3*30 = serial date 90 = Mar 30 1900
4*30 = serial date 120 = Apr 29 1900
5*30 = serial date 150 = May 29 1900
6*30 = serial date 180 = Jun 29 1900
7*30 = serial date 210 = Jul 28 1900
8*30 = serial date 240 = Aug 27 1900
9*30 = serial date 270 = Sep 26 1900
10*30 = serial date 300 = Oct 26 1900
11*30 = serial date 330 = Nov 25 1900
12*30 = serial date 360 = Dec 25 1900

(1) Excel incorrectly identifies Feb 29 1900 as a leap day/year. This is
well known and is "intentional".

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
For those who might be interested in this method, the numbers 28, 29 and
30 can all be used as the multiplier.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Try this:

=TEXT(n*30,"mmm")

Where n = 1 to 12

--
Biff
Microsoft Excel MVP


"Blaze-Of-Glory" wrote in
message ...
Excel 2007 - Why doesn't this work? Being forced to use VLookup and
make a
month table each time I want to convert a month number to a month nme

=Text(n,"mmm") always returns "Jan" when n=1..12
 

Excel incorrectly identifies Feb 29 1900 as a leap day/year:

http://support.microsoft.com/kb/214326

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Here's how it works...

**Only works using the default date system.

1*30 = serial date 30 = Jan 30 1900
2*30 = serial date 60 = Feb 29 1900 (1)
3*30 = serial date 90 = Mar 30 1900
4*30 = serial date 120 = Apr 29 1900
5*30 = serial date 150 = May 29 1900
6*30 = serial date 180 = Jun 29 1900
7*30 = serial date 210 = Jul 28 1900
8*30 = serial date 240 = Aug 27 1900
9*30 = serial date 270 = Sep 26 1900
10*30 = serial date 300 = Oct 26 1900
11*30 = serial date 330 = Nov 25 1900
12*30 = serial date 360 = Dec 25 1900

(1) Excel incorrectly identifies Feb 29 1900 as a leap day/year. This is
well known and is "intentional".

--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
For those who might be interested in this method, the numbers 28, 29 and
30 can all be used as the multiplier.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Try this:

=TEXT(n*30,"mmm")

Where n = 1 to 12

--
Biff
Microsoft Excel MVP


"Blaze-Of-Glory" wrote in
message ...
Excel 2007 - Why doesn't this work? Being forced to use VLookup and
make a
month table each time I want to convert a month number to a month nme

=Text(n,"mmm") always returns "Jan" when n=1..12
 

"T. Valko" wrote...
Here's how it works...

**Only works using the default date system.

1*30 = serial date 30 = Jan 30 1900
....

Right, so use 29 as the multiplier and it works in both date systems.