IF Statement Help...

Jav_uk8

New Member
Joined
Jan 12, 2017
Messages
22
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
 

Some videos you may like

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
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 12, 2017
Messages
22
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
Joined
Nov 7, 2006
Messages
8,350

ADVERTISEMENT

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
Joined
Feb 26, 2019
Messages
766
Office Version
  1. 365
Platform
  1. Windows
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," "))
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
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.
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top