Hi everyone, I am trying to calculate the time to completion per agent by using the following formula [(A-B)-C]:
(Talent__c.Timestamp_Contract_Signed_State__c - Talent__c.Employee_Invited_Date__c)-Talent__c.Elapsed_Time_Relocation_Immigration__c
(data type: date - formula(date) - formula(number))
It works fine besides the fact that I also need the result to be calculated only on business days.
I have tried then the following:
9 * (
( 5 * FLOOR( ( DATE( Talent__c.Timestamp_Contract_Signed_State__c ) - DATE( 1900, 1, 8) ) /
7) +
MIN(5,
MOD( DATE( Talent__c.Timestamp_Contract_Signed_State__c ) - DATE( 1900, 1, 8), 7) +
MIN( 1, 24 / 9 * ( MOD( Talent__c.Timestamp_Contract_Signed_State__c - DATE( '1900-01-08 08:00:00' ), 1 ) ) )
)
)
-
( 5 * FLOOR( ( DATE( Talent__c.Employee_Invited_Date__c) - DATE( 1900, 1, 8) ) /
7) +
MIN( 5,
MOD( DATE( Talent__c.Employee_Invited_Date__c ) - DATE( 1996, 1, 1), 7 ) +
MIN( 1, 24 / 9 * ( MOD( IF(AND(Talent__c.Employee_Invited_Date__c < DATE(TEXT(YEAR(DATE(Talent__c.Employee_Invited_Date__c))) + '-' + TEXT(MONTH(DATE(Talent__c.Employee_Invited_Date__c))) + '-' + TEXT(DAY(DATE(Talent__c.Employee_Invited_Date__c))) + ' 08:00:00'),Talent__c.Employee_Invited_Date__c >= DATE(TEXT(YEAR(DATE(Talent__c.Employee_Invited_Date__c))) + '-' + TEXT(MONTH(DATE(Talent__c.Employee_Invited_Date__c))) + '-' + TEXT(DAY(DATE(Talent__c.Employee_Invited_Date__c))) + ' 00:00:00')),DATE(TEXT(YEAR(DATE(Talent__c.Employee_Invited_Date__c))) + '-' + TEXT(MONTH(DATE(Talent__c.Employee_Invited_Date__c))) + '-' + TEXT(DAY(DATE(Talent__c.Employee_Invited_Date__c))) + ' 08:00:00'),Talent__c.Employee_Invited_Date__c) - DATE('1996-01-01 08:00:00'), 1) ) ) ) ) )-Talent__c.Elapsed_Time_Relocation_Immigration__c
What I am getting in the validation is the following error:
Error when encoding row-level formula: Incorrect number of parameters for function 'DATE()'. Expected 3, received 1
Any help to fix the error would be greatly appreciated! Thank you
Very likely this is the issue - a change in the data type to date/time probably will fix it as at the moment there is no time.