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.