IF Statement Help...

[Jav_uk8]

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

 

[tyija1995]

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.

 

[tyija1995]

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]

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]

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

 

[tyija1995]

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]

Thank you tyija1995!!! It works perfectly

 

[jtakw]

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.