hi guys new to this forum,
trying to write an excel nested if
here is the situation i have mm/year in A1.. 1/2011
from a1 to a4 i want to write out if A1 left "1" then make a1= 2, if its 2 then make a2 = 3
thats for changing month then i need another iffor the year.. the value of a1 left is 4 then a2 will change to 01/2012 and a3 will 3/2012
i was starting out with simple left but with 2 nested if's i get an error..
=IF(LEFT(A1)="1",LEFT(A1 +1),A1,IF(LEFT(A1)="2",LEFT(A1+1),A1))
you entered too many arguments for this function..
any ideas..
Last edited by NBVC; 01-13-2012 at 08:25 AM.
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))... try this function and keep copying the formula to right
is the date in A1 a date formatted as mm/yyyy or is it a text entry?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Date is text entry its actually coming from a WEBi report on portal , coming in Xcelsius, but i have used Excel on top of it. the first formula wont work as the date in the first field can change from 1 to 4. the dates are dynamic and they can change any time a new report is generated thats why i need an if to see what the first number is.
i could change the format to MM/YYYY.
On your formula, you just need to remove the A1 in the middle, eg.
=IF(LEFT(A1)="1",LEFT(A1 +1),IF(LEFT(A1)="2",LEFT(A1+1),A1))
or shorten to..
=IF(OR(LEFT(A1)={"1","2"}),LEFT(A1+1),A1)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
sorry guys that only worked for the first 2, i tried to use the comma and add the formuals for the 3rd and 4th fiedl and it errors out again. also how can i write it out for the right brace for the year. as when it shows 4/2011 then the next number will becomes 1/2012
this is what i used
=IF(LEFT(A1)="1",LEFT(A1 +1),IF(LEFT(A1)="2",LEFT(A1+1),A1),IF(LEFT(A1)="3",LEFT(A1+1),A1),IF(LEFT(A1)="4",LEFT(A1+1),A1))))
Try:
=A1+IF(LEFT(A1)+0<=4,1,0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks