ISNUMBER() Formula function not behaving as expected

Question

I am using a managed app that provides Local Time fields based on a Lead or Contact's country and phone number. Great stuff, very useful.

I am trying to create my own formula field, based on the Local Time field (a text field), that calcuates the number of hours left in a lead's business day (until 5pm local time). I want it to return NULL if the local time is outside business hours or is unknown (the app fills the Local Time field value as "Unknown" if there is no country or phone).

Here is my formula, which is working great except when the Local Time field shows "Unknown", in which case it displays "#Error!".

IF( 

OR( 

    VALUE(LEFT( tz__Local_Time_24_Short__c ,2)) >= 17, 
    VALUE(LEFT( tz__Local_Time_24_Short__c ,2)) < 9, 
    NOT(ISNUMBER(LEFT( tz__Local_Time_24_Short__c , 2))) 

),

NULL,

16 - VALUE(LEFT( tz__Local_Time_24_Short__c ,2)) 
+ (1 - (VALUE(RIGHT(LEFT(tz__Local_Time_24_Short__c,5),2)))/60)

)

It seems that the NOT(ISNUMBER(LEFT( tz__Local_Time_24_Short__c , 2))) line is failing to catch this exception for some reason. Anyone have some idea why?

P.S. - I tried using tz__Local_Time_24_Short__c == "Unknown" as my exception too, that also didn't work.

Answer 1

You need to check ISNUMBER first in your OR conditions. That way, it doesn't try to do the number comparisons in the case where it is NaN. If you put them first, it will evaluate them when it should not. As soon as OR hits true, it stops evaluating conditions further down the list.