#Formulas38 diskutieren mit

Hello @Heath Parks please have a look at this online help article on the same subject :

https://help.salesforce.com/s/articleView?id=sf.formula_examples_dates.htm&type=5

Find the Number of Weekdays Between Two Dates

Calculating how many weekdays passed between two dates is slightly more complex than calculating total elapsed days. In this example, weekdays are Monday through Friday. The basic strategy is to choose a reference Monday from the past and find out how many full weeks and any additional portion of a week have passed between the reference date and your date. These values are multiplied by five for a five-day work week, and then the difference between them is taken to calculate weekdays.

(5 * ( FLOOR( ( date_1 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_1 - DATE( 1900, 1, 8), 7 ) ) )
-
(5 * ( FLOOR( ( date_2 - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( date_2 - DATE( 1900, 1, 8), 7 ) ) )

PS : it will not handle if the case is in stand by within the overall period. It will just consider the difference between both dates

PPS : Could you please explain the IF rule you have mentioned on your post. How do you expect the calculation to be impacted ?

Eric

Hi @Alex Nis, This formula is calculating the percentage contribution of an objects amount against the Account’s total USD valuation.ABS({!$Record.Amount__c}*{!$Record.Currency__r.USD_FX_Rate__c} / {!$Record.Account_Name__r.USD_Valuation_Total__c}) * 100 

Please see the explanation for the each part in the formula,

• Amount__c → The current record amount. 
• Currency__r.USD_FX_Rate__c → Exchange rate used to convert the amount into USD. 
• Amount__c * USD_FX_Rate__c → Converts the amount into USD value. 
• USD_Valuation_Total__c → Total USD valuation from the related Account. 
• / USD_Valuation_Total__c → Finds what portion of the total this record represents. 
• ABS(...) → Ensures the result is always positive, even if the amount is negative. 
• * 100 → Converts the decimal into a percentage. 

Example If Amount is 500 ,  FX Rate is 1.2  and Account Total USD Valuation is 6000 means, It would be,

(500 * 1.2) / 6000 * 100= 600 / 6000 * 100= 10%

So the formula returns 10%

Hope this works for you!

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