Nested if and Left and right statements

**Dante_sparda** · 01-05-2012, 11:06 AM

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..

**adaws** · 01-05-2012, 11:12 AM

=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))... try this function and keep copying the formula to right

**NBVC** · 01-05-2012, 11:14 AM

is the date in A1 a date formatted as mm/yyyy or is it a text entry?

**Dante_sparda** · 01-05-2012, 11:27 AM

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.

**NBVC** · 01-05-2012, 11:33 AM

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)

**Dante_sparda** · 01-05-2012, 02:04 PM

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

**Dante_sparda** · 01-05-2012, 02:10 PM

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))))

**NBVC** · 01-05-2012, 03:09 PM

Try:

=A1+IF(LEFT(A1)+0<=4,1,0)

Nested if and Left and right statements

LinkBack

Thread Tools

Rate This Thread

Display

Nested if and Left and right statements

Re: Nested if and Left and right statements

Re: Nested if and Left and right statements

Re: Nested if and Left and right statements

Re: Nested if and Left and right statements

Re: Nested if and Left and right statements

Re: Nested if and Left and right statements

Re: Nested if and Left and right statements

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions