Date diff with multiple conditions

[WillSTARK]

Hi,

Currently I am computing the date difference in days using this formula (which works): =if(and(ISNUMBER(W2),ISNUMBER(H2)),W2-R2,"")

However, I would like to restrain the output and only consider values between 5 and 150.
So I would like the function to say If W2 isnumber and if H2 isnumber then, W2 - R2, "" and if W2 - R2 isnot between 5 and 150 range, ""

I tried various tweak but I did not manage to get it right, so any help would be appreciated.

Thanks

 

[FormR]

Welcome to the forum!

Maybe:
=IF(AND(ISNUMBER(W2),ISNUMBER(H2)),IF(OR(W2-R2<5,W2-R2>150),"",W2-R2),"")

Or slightly shorter:
=IF(COUNT(W2,H2)=2,IF(OR(W2-R2<5,W2-R2>150),"",W2-R2),"")

 

[WillSTARK]

Hi,
Thank you for your reply, it works! Instead of the 'OR' I had 'and', and forgot the "" in the end.
Thanks a lot,
Cheers

 

[GTO]

Hi there,

Just curious, why not check if R2 is a number?

Mark

 

[WillSTARK]

Hi,
I believe it was a mistake. The formula works, but I changed R2 to H2. You are right the first two cells verified as Isnumber should be the one used afterwards.

Hi there,

Just curious, why not check if R2 is a number?

Mark

 

[FormR]

Instead of the 'OR' I had 'and'

Hi, you could do it with AND() by switching the equality tests - and thinking about it, you could do away with the nested IF() - not that it saves that much.

=IF(AND(COUNT(W2,R2)=2,W2-R2>=5,W2-R2<=150),W2-R2,"")