+ Start a Discussion

Business days formula For Validation Field on Account

Hello - I am trying to calculate the number of business hours it took for one of my data analsys to triage a new Regestration (account), i need to have business hours between 7:00 am to 6:00pm and excluding weekends. 


Any help would be AWESOME!




Best Answer chosen by Admin (Salesforce Developers) 
Shannon HaleShannon Hale

All Answers

Shannon HaleShannon Hale
This was selected as the best answer

Thank you so much! This did help alot.



In addition to many of the formulas floating out there, I had to add the below to take care of Holidays.


Add the below line to say count one less business day for Christmas.


 - (if((MyTask_Due_Date__c > DATE(2013,12, 25)  && TODAY() > DATE(2013,12, 25) )  , 1, 0) )  


So, to calculate the Business days between two dates - that exclude Weekends AND Holiday(Christmas this year).


TODAY() - MyTask_Due_Date__c - 2*( floor((TODAY() - DATE(2009, 9, 14))/7) - floor((MyTask_Due_Date__c  - DATE(2009, 9, 14))/7) ) - (if((MyTask_Due_Date__c > DATE(2013,12, 25)  && TODAY() > DATE(2013,12, 25) )  , 1, 0) )


I know this is a manual way and it means you will need to remember to change the <year>, but if there is an easier way to read the Holidays from the setup, please share it here.