How to add ISERROR to IF statement:

[nbk95jg]

Hey everyone,

I have a formula running (in column AN) to calculate how long it took do something (in days)
=IF(AL2="Outstanding",DATEDIF(H2,TODAY(),"d"),IF(AL2="Final",DATEDIF(H2,T2,"d"),""))

Column AL2 = Status if the question is answered (final) or still needs to be answered (outstanding)

Column H2 = Date a question was created

Column T2 = Date the question was answered (therefore in a final status)

Sometimes due to system issues out of my control, column H will have a date after the date in column T2, therefore the formula returns #NUM!

In other words:

working scenario:
H: 10/4/12
T: 10/7/12
AL: Final
AN: 3


#NUM! scenario:
H: 10/4/12
T: 10/3/12
AL: Final
AN: #NUM!


How can I add ISERROR to ignore the #NUM! responses?

Thanks

 

[blossomthe2nd]

Hi nbk95jg,
Try

=IF(ISERROR(IF(AL2="Outstanding",DATEDIF(H2,TODAY(),"d"),IF(AL2="Final",DATEDIF(H2,T2,"d"),""))),"",IF(AL2="Outstanding",DATEDIF(H2,TODAY(),"d"),IF(AL2="Final",DATEDIF(H2,T2,"d"),"")))

A

 

[Jonmo1]

You don't have to test the whole formula for error if you already know what causes the error (H > T)
In this case, you only have to test for that condition.

Try
=IF(H2>T2,"",IF(AL2="Outstanding",DATEDIF(H2,TODAY(),"d"),IF(AL2="Final",DATEDIF(H2,T2,"d"),"")))

 

[nbk95jg]

[TABLE="width: 448"]
<TBODY>[TR]
[TD="colspan: 7"]</SPAN>@blossomthe2nd: Thanks for the response, your formula works wonderfully

@jonmo1: Although your formula works too, it drops one feature of the formula, T will be blank/missing a date if the question is still outstanding, the IF(H2>T2,"", part makes AN return blank, instead of calculating the amount of time the question has been outstanding. The formula needs to calculate the amount of days the question is either outstanding (counting daily, until it's in a final status), or it counts the amount of days it took to get in a final status.

Thanks to both though, super fast reponses..
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL span=7></COLGROUP>[/TABLE]

 

[Scott Huish]

You still don't need ISERROR. You also don't need DATEDIF for day calculations, you can just subtract them.
Always better to check for a known condition than to throw an ISERROR around it.

Wouldn't this do what you want?
=ABS(H2-IF(AL2="outstanding",TODAY(),T2))

 

[nbk95jg]

@HOTPEPPER: Your formula does return a value, but it puts a negative in the days where H>T, I need that to remain blank.

 

[Scott Huish]

So does blossom's formula, I matched the output exactly. It also can't return a negative value. Absolute Value never returns a negative number.

 

[Scott Huish]

How about:

=IF(H2>T2,"",IF(AL2="outstanding",TODAY(),T2)-H2)

 

[Jonmo1]

For my formula in Post #3, we just need to re-arrange where the H>T test is..

Try
=IF(AL2="Outstanding",DATEDIF(H2,TODAY(),"d"),IF(AL2="Final",IF(H2>T2,"",DATEDIF(H2,T2,"d")),""))