#Formulas53 discussing

I should point out that the record type I am wanting to display a value against is one ('Site'), but the value/result is coming from 'Asset'.  So, If Site record 'A' contains an Asset Record A, B & C which is water related, then Site record A will contain water.  If Site 'A' doesn't contain any Asset records in it that are water related, then the result will be 'no'. 

My thought was I could create a formula lookup on asset record type, and then create another formula to display on site that looked up the value on assets within, but frustratingly salesforce won't let you create formulas looking up other formulas

a few things:  

First: Validation Rule Formulas can't read the full 18 character ID, they can only the 15 character ID  

Second:  Don't use hard coded ID in Formulas, use RecordType Name or DeveloperName   

Third:  Don't use ISNULL, that Function has been deprecated by Salesforce like 10+ years ago   

I would write it like this

AND(
RecordType.Name <> 'Hi My Name Is', 
OR( 
Amount = 0, 
ISBLANK(Amount)
), 
CASE( StageName,
'Pledged', 1, 
'Received', 1, 
'Awarded - Open', 1, 
'Awarded - Closed', 1, 
0 ) = 1
)

Hi Meg, 

This is due to the fact that string formula cannot deal with the UTC wrap-around. It would be simpler to shift the time prior to evaluating the hour. 

Consider this: 

HOUR(TIMEVALUE(CreatedDate - (4/24))) >= 8 && 

HOUR(TIMEVALUE(CreatedDate - (4/24))) < 22 

Why does it work? 

Time Shifted: Shifting by 4/24 shifts UTC time to EST. 

Error-Free: Salesforce takes care of the 24-hour wrapping internally; therefore, no errors occur at midnight. 

Easier: Simply use the HOUR function rather than complicated string manipulation.

In any case, everything will depend on what you need the value of the 'Gap' to reflect. In case you need to determine how many days have gone after the meeting, then your formula is fine, only the returned result should be positive, otherwise you can simply move the dates in your formula around. In case you need just a positive value irrespective of what date is earlier, you can simply put an ABS function around your formula: 

ABS(DATEVALUE(LastModifiedDate) - Date__c) 

Now you won't need to change anything if someday a meeting date changes position!

Apologies, I forgot to reply.

I see a typo in my formula, there is a "8*60+300" that should be "8*60+30":

((DATEVALUE(date_time_2__c )-DATEVALUE(date_time_1__c )-1)*8.5
+
((17*60+00)
-
(
IF(CASE(WEEKDAY(DATEVALUE(date_time_1__c)),1,1,7,1,0)=1,8*60+30,
MIN(17*60+00,
MAX(HOUR(TIMEVALUE(date_time_1__c ))*60+MINUTE(TIMEVALUE(date_time_1__c )),8*60+30)))))/60
+
(
IF(CASE(WEEKDAY(DATEVALUE(date_time_2__c)),1,1,7,1,0)=1,17*60+0,
MIN((MAX(8*60+30,HOUR(TIMEVALUE(date_time_2__c ))*60+MINUTE(TIMEVALUE(date_time_2__c )))),(17*60+0)))
-
(8*60+30))/60)
-
8.5*(
FLOOR((DATEVALUE(date_time_2__c)- DATEVALUE(date_time_1__c))/7)*2
+
IF(AND(WEEKDAY(DATEVALUE(date_time_1__c))=1, WEEKDAY(DATEVALUE(date_time_2__c))<>7),1,
IF(CASE(WEEKDAY(DATEVALUE(date_time_1__c)),1,8,WEEKDAY(DATEVALUE(date_time_1__c)))>CASE(WEEKDAY(DATEVALUE(date_time_2__c)),1,8,WEEKDAY(DATEVALUE(date_time_2__c))),2,
IF(OR (WEEKDAY(DATEVALUE(date_time_2__c))=7, WEEKDAY(DATEVALUE(date_time_1__c))=1),1,
IF(OR (WEEKDAY(DATEVALUE(date_time_2__c))=1, WEEKDAY(DATEVALUE(date_time_1__c))=7),2,
0))))
)

Also, it will return 0.25 in your example since it is only a quarter of an hour since 8.30 on the 14th. a quarter of 1(hour) returns 0.25 in my formula

Hello @Mario Santellan, missed it was for a default value.  Indeed the record does not exist yet in this case, so no CreatedDate. Replace indeed the DATEVALUE(CreatedDate) by TODAY(), as you have done in your previous post :

TODAY() +  
CASE( 
    WEEKDAY(TODAY()), 
    5, 4,
    6, 4,
    7, 3,
    2
)

Eric

The Pcinfotools NSF to PST Converter provides users with an effective method to transform NSF files into PST format, which Outlook supports. This process enables users to transfer their data from IBM Lotus Notes to Outlook while maintaining all their essential information. Users should choose their desired NSF file through the provided selection option for conversion purposes. The program performs a file upload, which enables users to view mailbox contents that include emails and contacts and calendars and attachments. Users have the option to choose particular folders for conversion, or they can choose to convert all database content. Users should select PST as their desired output format while they designate a specific location for the resulting file. Users should initiate the process by selecting the Convert button. The tool provides secure and accurate conversion capabilities that maintain all folder structures and email formatting and metadata and attachments to ensure dependable