Skip to content

Instantly share code, notes, and snippets.

@justin-lyon

justin-lyon/calc-business-days-formula

Created June 30, 2022 11:01
Show Gist options
  • Save justin-lyon/7c8b5b4f68d631edec28d3e1f754ccca to your computer and use it in GitHub Desktop.
Save justin-lyon/7c8b5b4f68d631edec28d3e1f754ccca to your computer and use it in GitHub Desktop.
Download ZIP
salesforce formula field to compare two Date Fields
Raw
calc-business-days-formula
(IF(AND((5 - (CASE(MOD( FirstDate__c - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) < (CASE(MOD( SecondDate__c - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) ),
((( SecondDate__c - FirstDate__c ) + 1) < 7)),
((CASE(MOD( SecondDate__c - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0)) - (5 - (CASE(MOD( FirstDate__c - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)))),
(((FLOOR((( SecondDate__c - FirstDate__c ) - (CASE(MOD( FirstDate__c - DATE(1900, 1, 6), 7), 0, 0, 1, 6, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0))) / 7)) * 5) +
(CASE(MOD( FirstDate__c - DATE(1900, 1, 6), 7), 0, 0, 1, 5, 2, 5, 3, 4, 4, 3, 5, 2, 6, 1, 0)) +
(CASE(MOD( SecondDate__c - DATE(1900, 1, 6), 7), 0, 0, 1, 0, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 0))))-1)
@justin-lyon
Copy link
Author

Returns the difference between two date fields in days. FirstDate__c must be older than SecondDate__c

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment