| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to return a month name from a number (1-12) that is not a
Hi,
I need to return the month name (Jan, Feb, etc.) from a number (1-12). However the number is not a date. I know can set up a range containing the month names and do a vlookup but I was wondering if there is a more compact and effecient way to do this with a function. -- TIA Chuck M. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to return a month name from a number (1-12) that is not a
=TEXT(DATE(2007,A1,1),"mmm")
A1 contains 1 to 12 "Chuck M" wrote: Hi, I need to return the month name (Jan, Feb, etc.) from a number (1-12). However the number is not a date. I know can set up a range containing the month names and do a vlookup but I was wondering if there is a more compact and effecient way to do this with a function. -- TIA Chuck M. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to return a month name from a number (1-12) that is not a
Hi Chuck,
=TEXT(DATE(0,A1,1),"mmmm") -- Kind regards, Niek Otten Microsoft MVP - Excel "Chuck M" wrote in message ... | Hi, | I need to return the month name (Jan, Feb, etc.) from a number (1-12). | However the number is not a date. I know can set up a range containing the | month names and do a vlookup but I was wondering if there is a more compact | and effecient way to do this with a function. | | -- | TIA | Chuck M. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to return a month name from a number (1-12) that is not a
You could use this function:
=CHOOSE(A1,"Jan","Feb","Mar","Apr","May","Jun","Ju l","Aug","Sep","Oct","Nov","Dec") where A1 contains a number 1 - 12. Hope this helps. Pete On Jul 26, 2:20 pm, Chuck M wrote: Hi, I need to return the month name (Jan, Feb, etc.) from a number (1-12). However the number is not a date. I know can set up a range containing the month names and do a vlookup but I was wondering if there is a more compact and effecient way to do this with a function. -- TIA Chuck M. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to return a month name from a number (1-12) that is not a
One way
=TEXT(1*29,"mmmm") Note the 1 could be a cell reference Another way =CHOOSE(1,"January","Febuary","March","April","May ","June","July","August","September","October","No vember","December") And lastly with a table 1 Jan 2 Feb etc =VLOOKUP(1,A1:B12,2,FALSE) Mike "Chuck M" wrote: Hi, I need to return the month name (Jan, Feb, etc.) from a number (1-12). However the number is not a date. I know can set up a range containing the month names and do a vlookup but I was wondering if there is a more compact and effecient way to do this with a function. -- TIA Chuck M. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to return a month name from a number (1-12) that is n
Thanks Toppers, Niek, Mike and Pete. The responses are greatly appreciated!
-- Chuck M. "Pete_UK" wrote: You could use this function: =CHOOSE(A1,"Jan","Feb","Mar","Apr","May","Jun","Ju l","Aug","Sep","Oct","Nov","Dec") where A1 contains a number 1 - 12. Hope this helps. Pete On Jul 26, 2:20 pm, Chuck M wrote: Hi, I need to return the month name (Jan, Feb, etc.) from a number (1-12). However the number is not a date. I know can set up a range containing the month names and do a vlookup but I was wondering if there is a more compact and effecient way to do this with a function. -- TIA Chuck M. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to return a month name from a number (1-12) that is not a
Another way
Format the cell as FormatCellsNumberCustom mmm =A1*30 -- Regards Roger Govier "Chuck M" wrote in message ... Hi, I need to return the month name (Jan, Feb, etc.) from a number (1-12). However the number is not a date. I know can set up a range containing the month names and do a vlookup but I was wondering if there is a more compact and effecient way to do this with a function. -- TIA Chuck M. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Function to return a month name from a number (1-12) that is not a
The problem with any of the methods that rely on a date format is that
you don't have much control over what is returned - either Jan, Feb, Mar (with a format of mmm), or January, February, March (with mmmm). If the OP wanted JAN, FEB, MAR (although admittedly he didn't ask for this) then he could use UPPER around the TEXT formulae, or specify this in the CHOOSE formulae, but you don't have this choice with pure formatting. I like your lateral thinking, though, Roger, relying on the date bug for Feb 1900 - well done! Pete On Jul 27, 5:55 pm, "Roger Govier" wrote: Another way Format the cell as FormatCellsNumberCustom mmm =A1*30 -- Regards Roger Govier "Chuck M" wrote in message ... Hi, I need to return the month name (Jan, Feb, etc.) from a number (1-12). However the number is not a date. I know can set up a range containing the month names and do a vlookup but I was wondering if there is a more compact and effecient way to do this with a function. -- TIA Chuck M.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
| Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
| Function or formula to convert "text" month to number of month | Excel Discussion (Misc queries) | |||
| Is there a function to return all Thursdays in a given month/yr | Excel Worksheet Functions | |||
| Is there a function that will return the page number? | Excel Worksheet Functions | |||
Linear Mode
