Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert number to month (2003 SP1)

    Is there a function that can convert a number from 1 through 12 to a month, without multiple nested if statements? For example, if a formula calculates a value as 8, I want a function that will return "August".

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert number to month (2003 SP1)

    With the month number in A1, use this formula:

    =TEXT(DATE(2005,A1,1),"mmmm")

    If you prefer Jan, Feb, Mar etc., use "mmm" instead of "mmmm".

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    USA
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert number to month (2003 SP1)

    You could also use the following Choose function (assuming that the cell with the month-determining value is A1):

    =CHOOSE(A1,"January","February","March","April","M ay","June","July","August","September","October"," November","December")

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert number to month (2003 SP1)

    That will certainly work, but apart from being quite long, it is also language-dependent. The formula I posted lets the system figure out the month names, so if someone with a Spanish language system opens the workbook, it'll show enero, febrero, marzo etc. instead of January, February, March.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert number to month (2003 SP1)

    Hans, I notice that with this formula if A1 is empty or has a 0 (zero) it results in Dec showing up. What changes are required to have no month show up? I assume that the ISBLANK Function comes into play for the empty cell but not sure of the syntax. Also not sure if the user's formula would ever result in zero showing up, but there would need to be someway to check the value before the calulation is run.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert number to month (2003 SP1)

    You could use
    <code>=IF(A1>0,TEXT(DATE(2005,A1,1),"mmmm"),"")</code>
    This will show an error if A1 contains a text value. If you want to suppress that too, use
    <code>=IF(AND(ISNUMBER(A1),A1>0),TEXT(DATE(2005,A1 ,1),"mmmm"),"")</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •