Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




=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 
#2




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:
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.worksheet.functions




=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 €žBlazeOfGlory€ť 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 
#4
Posted to microsoft.public.excel.worksheet.functions




=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 "BlazeOfGlory" 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 
#5
Posted to microsoft.public.excel.worksheet.functions




=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 "BlazeOfGlory" 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 
#6
Posted to microsoft.public.excel.worksheet.functions




=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 "BlazeOfGlory" 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 
#7
Posted to microsoft.public.excel.worksheet.functions




=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. "BlazeOfGlory" 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 
#8
Posted to microsoft.public.excel.worksheet.functions




=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) "BlazeOfGlory" 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. "BlazeOfGlory" 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 
#9
Posted to microsoft.public.excel.worksheet.functions




=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 "BlazeOfGlory" 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 
#10
Posted to microsoft.public.excel.worksheet.functions




=Text(n,"mmm") always returns "Jan" when n=1..12
BlazeOfGlory 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 online 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 builtin 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 addin. You could still use an addin to provide custom functions in your current Excel version. 
#11
Posted to microsoft.public.excel.worksheet.functions




=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 "BlazeOfGlory" 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 
#12
Posted to microsoft.public.excel.worksheet.functions




=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 "BlazeOfGlory" 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 
#13
Posted to microsoft.public.excel.worksheet.functions




=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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Excel  Golf  how to display "2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell  Excel Discussion (Misc queries)  
change "true" and "false" to "availble" and "out of stock"  Excel Worksheet Functions  
HELP on "left","right","find","len","substitute" functions  Excel Discussion (Misc queries)  
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next  New Users to Excel  
Insert "" in text "1234567890" to have a output like this"1234567890"  Excel Discussion (Misc queries) 