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


[TABLE="width: 500"]
<tbody>[TR]
[TD]F[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]Employee Time[/TD]
[/TR]
[TR]
[TD]10/5/2016[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl1370, width: 104, align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10/5/2016[/TD]
[TD]2016-09-27[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl1370, width: 64"]2016-10-03[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl1370, width: 104, align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]10/5/2016[/TD]
[TD]2015-10-13[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl1370, width: 64"]2016-08-26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl1370, width: 104, align: right"]358[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

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