Question on IF, IFERROR, IFNA formula

[rjhemphill]

I am having trouble getting my IF, IFERROR,IFNA statements to not populate #N/A or #Value


Here is my current Formula
=IF(B2="Employee",ABS(F2-M2),IF(B2<>"Employee",ABS(N2-M2)))

the issue i am having is that some of the M and N columns don't have dates. so i am getting #Value populated

F	M	N	Employee Time
10/5/2016			#VALUE! <tbody> </tbody>
10/5/2016	2016-09-27	2016-10-03 <tbody> </tbody>	8 <tbody> </tbody>
10/5/2016	2015-10-13	2016-08-26 <tbody> </tbody>	358 <tbody> </tbody>

<tbody>
</tbody>

 

[Momentman]

So what do you want to display if either M or N is blank

you can do,

=IF(OR(M2="",N2=""),"",IF(B2="Employee",ABS(F2-M2),IF(B2<>"Employee",ABS(N2-M2))))

That way, it returns a blank if either off M2 or N2 is blank

N:B didn't even check for redundancy in the formula, just added another IF to an already existing unneeded IF

 

[Scott Huish]

Your 2nd IF is unnecessary because if B2 wasn't Employee in the first check, it would then not be equal to it.

You shouldn't get #VALUE unless your blank cells aren't actually blank, perhaps they contain a space or empty string ""

=IFERROR(ABS(IF(B2="Employee",F2-M2,N2-M2)),"")

 

[McMasher]

You could also just do

[COLOR=#333333]Iferror(IF(B2="Employee",ABS(F2-M2),IF(B2<>"Employee",ABS(N2-M2))),"")

or for a more in-depth explanation

Iferror([/COLOR][COLOR=#333333]IF(B2="Employee",ABS(F2-M2),IF(B2<>"Employee",ABS(N2-M2))),if(and(M2="",N2=""),"No Dates",if(M2="","No Begin Date","No End Date")))[/COLOR]

 

[rjhemphill]

Thanks This worked Great.