Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a maximum for a formula?

    I am trying to have excel look at the first two digits of a date...=month(cell) and then I have a formula to tell me the NAME of the month..

    =If(c2=1,"JAN",if(c2=2, "FEB",....

    it works until I get to Aug...then I get an error message telling me my formula is incorrect and it highlights at Aug.

    Thanks for any help!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You're running into the maximum of 7 levels of nested functions.
    If you have a date in cell B2, you can either format it with the custom format mmm, or link to it in C2 with the formula =B2 and format C2 as mmm.
    Yet another possibility is to use =TEXT(B2,"mmm") in C2.
    If you prefer to have the name in upper case: =UPPER(TEXT(B2,"mmm"))

  3. #3
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    There is a maximum of 8 nested IFs.

    You could use a formula like
    =HLOOKUP(C2,{1,2,3,4,5,6,7,8,9,10,11,12;"Jan","Feb ","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct", "Nov","Dec"},2,TRUE)
    to achieve the result you want
    You could replace the array {1,2,3,4,5,6,7,8,9,10,11,12;"Jan","Feb","Mar","Apr ","May","Jun","Jul","Aug","Sep","Oct","Nov","D ec"} with a references to cells on the worksheet that contain these values.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Or you could set up a small table elsewhere and use the LOOKUP function, or for more versatility, INDEX/MATCH combo.

Posting Permissions

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