=Text(n,"mmm") always returns "Jan" when n=1..12
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
Stefi
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:
Jacob Skaria
Try
=TEXT(date(year(today()),n,1),"mmm")
If this post helps click Yes
---------------
Jacob Skaria
David Biddulph
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" <Blaze-O...@discussions.microsoft.com> wrote in message
news:2667F915-DFD3-4B3D...@microsoft.com...
T. Valko
Try this:
=TEXT(n*30,"mmm")
Where n = 1 to 12
--
Biff
Microsoft Excel MVP
"Blaze-Of-Glory" <Blaze-O...@discussions.microsoft.com> wrote in message
news:2667F915-DFD3-4B3D...@microsoft.com...
Blaze-Of-Glory
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.
Rick Rothstein
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" <BlazeO...@discussions.microsoft.com> wrote in message
news:4894961A-F65B-4D31...@microsoft.com...
Harlan Grove
> . . .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.
Rick Rothstein
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" <biffi...@comcast.net> wrote in message
news:uyxGnSy$JHA....@TK2MSFTNGP02.phx.gbl...
T. Valko
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" <rick.new...@NO.SPAMverizon.net> wrote in message
news:uIlRbGz$JHA....@TK2MSFTNGP02.phx.gbl...
T. Valko
Excel incorrectly identifies Feb 29 1900 as a leap day/year:
http://support.microsoft.com/kb/214326
--
Biff
Microsoft Excel MVP
"T. Valko" <biffi...@comcast.net> wrote in message
news:%23lgvEyz$JHA....@TK2MSFTNGP02.phx.gbl...
Harlan Grove
>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.