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

  • Thread starter Thread starter Blaze-Of-Glory
  • Start date Start date
B

Blaze-Of-Glory

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
 
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:
 
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.
 
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.
 
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.
 
For those who might be interested in this method, the numbers 28, 29 and 30
can all be used as the multiplier.
 
Blaze-Of-Glory said:
. . .It's not at all clear from the help for
text() that the date format expects epoch time. . . .
Click to expand...

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.
Click to expand...

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.
 
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".
 
T. Valko said:
Here's how it works...

**Only works using the default date system.

1*30 = serial date 30 = Jan 30 1900
Click to expand...
....

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