Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to add more than 7 if nested statements. 7 is the limit.
I am trying to show 12 month names from dates, but can show only 8 since if
statments can be nested 7 times. Eg of my data and formula: =IF(MONTH(D2)=10,"Oct04",IF(MONTH(D2)=11,"Nov04",I F(MONTH(D2)=1,"Jan05",IF(MONTH(D2)=2,"Feb05",IF(MO NTH(D2)=3,"Mar05",IF(MONTH(D2)=12,"Dec04",IF(MONTH (D2)=4,"Apr05"))))))) I need to join the if statment to show the rest of the months. Thanks. RRS. |
#3
|
|||
|
|||
=TEXT(A1,"mmm")&"04"
but probably this suits you better: =TEXT(A1,"mmm")&TEXT(A1,"yy") -- Kind regards, Niek Otten Microsoft MVP - Excel "RRS from Connecticut" <RRS from wrote in message ... I am trying to show 12 month names from dates, but can show only 8 since if statments can be nested 7 times. Eg of my data and formula: =IF(MONTH(D2)=10,"Oct04",IF(MONTH(D2)=11,"Nov04",I F(MONTH(D2)=1,"Jan05",IF(MONTH(D2)=2,"Feb05",IF(MO NTH(D2)=3,"Mar05",IF(MONTH(D2)=12,"Dec04",IF(MONTH (D2)=4,"Apr05"))))))) I need to join the if statment to show the rest of the months. Thanks. RRS. |
#4
|
|||
|
|||
Hi,
One possibility: Create a helper list in two columns (say X1:X12, Y1:Y12) and populate them with numbers and month/year-info (1 in X1 and Jan-05 in Y1,......10 in X10 and Oct-04 in Y10,...12 in X12 and Dec-04 in Y12). Now use the formula in, say E2, =VLOOKUP(MONTH(D2),$X$1:$Y$12,2) Regards, B. R. Ramachandran "RRS from Connecticut" wrote: I am trying to show 12 month names from dates, but can show only 8 since if statments can be nested 7 times. Eg of my data and formula: =IF(MONTH(D2)=10,"Oct04",IF(MONTH(D2)=11,"Nov04",I F(MONTH(D2)=1,"Jan05",IF(MONTH(D2)=2,"Feb05",IF(MO NTH(D2)=3,"Mar05",IF(MONTH(D2)=12,"Dec04",IF(MONTH (D2)=4,"Apr05"))))))) I need to join the if statment to show the rest of the months. Thanks. RRS. |
#5
|
|||
|
|||
Just to add another variation to the answers already received from Bernie
and Niek, you could just format cell D2 (and the rest of the column if required) as Format CellsNumberCustom mmmyy Regards Roger Govier RRS from Connecticut wrote: I am trying to show 12 month names from dates, but can show only 8 since if statments can be nested 7 times. Eg of my data and formula: =IF(MONTH(D2)=10,"Oct04",IF(MONTH(D2)=11,"Nov04",I F(MONTH(D2)=1,"Jan05",IF(MONTH(D2)=2,"Feb05",IF(MO NTH(D2)=3,"Mar05",IF(MONTH(D2)=12,"Dec04",IF(MONTH (D2)=4,"Apr05"))))))) I need to join the if statment to show the rest of the months. Thanks. RRS. |
#6
|
|||
|
|||
The beauty of simplicity!
-- Kind regards, Niek Otten Microsoft MVP - Excel "Roger Govier" wrote in message ... Just to add another variation to the answers already received from Bernie and Niek, you could just format cell D2 (and the rest of the column if required) as Format CellsNumberCustom mmmyy Regards Roger Govier RRS from Connecticut wrote: I am trying to show 12 month names from dates, but can show only 8 since if statments can be nested 7 times. Eg of my data and formula: =IF(MONTH(D2)=10,"Oct04",IF(MONTH(D2)=11,"Nov04",I F(MONTH(D2)=1,"Jan05",IF(MONTH(D2)=2,"Feb05",IF(MO NTH(D2)=3,"Mar05",IF(MONTH(D2)=12,"Dec04",IF(MONTH (D2)=4,"Apr05"))))))) I need to join the if statment to show the rest of the months. Thanks. RRS. |
#7
|
|||
|
|||
Thanks Niek
If only I could think that way always!!! I usually use the sledgehammer. Regards Roger Govier Niek Otten wrote: The beauty of simplicity! |
#8
|
|||
|
|||
On Fri, 23 Sep 2005 08:00:02 -0700, "RRS from Connecticut" <RRS from
wrote: I am trying to show 12 month names from dates, but can show only 8 since if statments can be nested 7 times. Eg of my data and formula: =IF(MONTH(D2)=10,"Oct04",IF(MONTH(D2)=11,"Nov04", IF(MONTH(D2)=1,"Jan05",IF(MONTH(D2)=2,"Feb05",IF(M ONTH(D2)=3,"Mar05",IF(MONTH(D2)=12,"Dec04",IF(MONT H(D2)=4,"Apr05"))))))) I need to join the if statment to show the rest of the months. Thanks. RRS. Since we are giving you all sorts of solutions, I thought I'd throw out another: =CHOOSE(MONTH(D2),"Jan05","Feb05","Mar05","Apr05", ...,etc) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Nested If Statements | Excel Discussion (Misc queries) | |||
Nested IF statements | Excel Worksheet Functions | |||
Employing constant arrays to limit nested IF statements. | Excel Worksheet Functions | |||
nested statements | New Users to Excel | |||
Do I need nested IF statements? | Excel Worksheet Functions |