Hello Everyone, In this post, I am
going provide a formula example that explains how to calculate end date after adding some days in start date. In this formula weekends(saturdays and sundays) will excluded. So let's get started.
Formula to Calculate date excluding weekends
Use following formula to calculate end date by excluding weekends.
Formula Return Type - Number
CASE(
MOD(StartDate__c - DATE(1900, 1, 7), 7),
0, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c-1)/5)*2,
1, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c)/5)*2,
2, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+1)/5)*2,
3, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+2)/5)*2,
4, (StartDate__c) + NumberOfDays__c + FLOOR((NumberOfDays__c+3)/5)*2,
5, (StartDate__c) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2,
6, (StartDate__c) - IF(NumberOfDays__c>0,1,0) + NumberOfDays__c + CEILING((NumberOfDays__c)/5)*2,
null)
Output:
Hope you like this post, for any feedback or suggestions please feel free to comment. I would appreciate your feedback and suggestions.
Thank you.
2 Comments
Thanks for sharing, very useful. Can this formula be updated to exclude holidays defined in the Business Hour?
ReplyDeleteGreat post! Calculating dates while excluding weekends is a common requirement in Salesforce, especially for project deadlines, case resolution times, or task planning. Your formula-based approach is clear and practical. It’s impressive how much can be achieved using native formulas without resorting to Apex code. For teams planning to migrate data from Salesforce to Zoho CRM, it’s equally important to ensure that business logic like this—especially around working days—is preserved in the new system. Thanks for sharing such a useful tip that enhances productivity and accuracy in date calculations!
ReplyDeletePost a Comment