Can anyone assist or point me in the direction of an example of a formula field that calculates days between two date/time fields excluding weekends?

  • 1
    @NicoleY, in the future, make sure you do your research prior to posting. If you do not find the info, make sure you do mention where you searched and any links that point towards related documentation that you may not understand so we can explain. Thanks. – glls Jan 8 '18 at 21:00

There's an example of this in Help & Training, shown below:

CASE(MOD( StartDate__c - DATE(1985,6,24),7), 

  0 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 
  1 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 
  2 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 
  3 , CASE( MOD( EndDate__c - StartDate__c ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 
  4 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 
  5 , CASE( MOD( EndDate__c - StartDate__c ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 
  6 , CASE( MOD( EndDate__c - StartDate__c ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 
  (FLOOR(( EndDate__c - StartDate__c )/7)*5) 

Where StartDate__c represents a field to use as the earlier of two dates, and EndDate__c represents a field to use as the later of two dates.

  • Thanks @sfdcfox for the answer. I'm not getting that how the above formula is ignored Holidays apart from Saturday and Sunday. – Anurag Bhardwaj Oct 5 at 13:21
  • @AnuragBhardwaj I don't understand your comment? Can you please clarify? – sfdcfox Oct 5 at 22:49
  • Ohh, my bad. My intent of asking is how above formula would ignore Public holidays mentioned in Business Hours object. Do we have any such formula to use BusinessHours sObject? I don't want Apex class solution. Please let me know if it has some sense. – Anurag Bhardwaj Oct 7 at 5:14
  • @AnuragBhardwaj I'm not aware of any such solution, though you might just try asking a new question with what you're trying to do, and what you've already tried. We'll be glad to try and help you solve your problem. – sfdcfox Oct 7 at 10:23
  • Thanks @sfdcfox – Anurag Bhardwaj Oct 10 at 18:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.