# IF Statement Help...

#### Jav_uk8

##### New Member
Hi All,

I am trying to get the following IF statement to work but struggling.

I want the following to calculate the days between, if there is a date in T11 or Y11 (leave blank if empty). Then the second formula onwards is to calculate the amount of days between dates if in Q11 equals to M1, M2, D, E, MW or SV

=IF(AND(ISBLANK(T11),ISBLANK(Y11))," ",IF(Q11="M1",Y11-T11," ",IF(Q11="M2",Y11-T11," ",IF(Q11="D",Y11-T11," ",IF(Q11="E",Y11-T11," ",IF(Q11="MW",Y11-T11," ",IF(Q11="SV",Y11-T11," "))))

Hope this makes sense?

Jav

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### tyija1995

##### Well-known Member
Hi Jav,

Try this:
IF(AND(ISBLANK(T11),ISBLANK(Y11))," ",IF(Q11="M1",Y11-T11,IF(Q11="M2",Y11-T11,IF(Q11="D",Y11-T11,IF(Q11="E",Y11-T11,IF(Q11="MW",Y11-T11,IF(Q11="SV",Y11-T11," ")))))))

Your formula was closing the IF statements too many times, you were putting " " if false, and then opening another IF statement, to nest you have to keep using IF for the "value if false" parameter, and then end with " " when all of them are written! Hope this helps.

Last edited:

#### tyija1995

##### Well-known Member
Just noticed because it is always checking for Q11 to equal a value & the result if true is always Y11-T11 then you can shortern the formula and use this:
IF(AND(ISBLANK(T11),ISBLANK(Y11))," ",IF(OR(Q11="M1",Q11="M2",Q11="D",Q11="E",Q11="MW",Q11="SV"),Y11-T11," "))

#### Jav_uk8

##### New Member
Hi tyija1995,

It has almost worked, the formula is trying to calculate the days between T - Y. Column T has a date but column Y is empty, the formula is returning -42974 (Because the date 27/08/2017 is entered in column T.

Jav

#### Special-K99

##### Well-known Member

If(and(t11<>"",y11<>""),if(or(q11="m1",q11="m2",q11="d",q11="e",q11="mw",q11="sv"),y11-t11,""))

You probably don't need a space if it fails, just a null.

Or even

IF(AND(T11<>"",Y11<>""),IF(ISNUMBER(SEARCH(Q11&",","M1,M2,D,E,MW,SV,")),Y11-T11,""))

Last edited:

#### tyija1995

##### Well-known Member
Ahh I see. That would be because you are essentially taking 0 - 27/08/2017 (which is 42974) as Excel does dates as numbers starting from 01/01/1900

Try this formula instead (Changed the AND to an OR at the start):
IF(OR(ISBLANK(T11),ISBLANK(Y11))," ",IF(OR(Q11="M1",Q11="M2",Q11="D",Q11="E",Q11="MW",Q11="SV"),Y11-T11," "))

#### Jav_uk8

##### New Member
Thank you tyija1995!!! It works perfectly

#### jtakw

##### Well-known Member
Hi,

Try this shorter version:

=IF(OR(T11="",Y11=""),"",IF(OR(Q11={"M1","M2","D","E","MW","SV"}),Y11-T11,""))

Note: Try not to use a Space ( " " ) as result, unless you have a Particular reason for doing so, it'll just cause problems down the road.

Replies
3
Views
127
Replies
2
Views
53
Replies
1
Views
62
Replies
2
Views
49
Replies
7
Views
348

1,109,353
Messages
5,528,199
Members
409,807
Latest member
nicky736

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...