1

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.

  • Try moving it to be the first OR condition. – Adrian Larson Jan 20 '15 at 18:39
  • Ok, that worked. Why on earth does it care about the order of the OR arguments? – OBerm Jan 20 '15 at 18:47
3

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.

  • OK I see - the error was actually coming from the first comparison in the OR(), not because the ISNUMBER() check failed and allowed the formula to attempt the calculation steps. – OBerm Jan 20 '15 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.