Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted

# Worflow that uses a the Count week day formula.

I have been trying to get the published solution of omitting weekends when calculating between two dates in a workflow but my limited skills hit rock bottom.  And that is why I need the forum's help.

I need to assign recurring tasks to our Reps initially every 7 days then every 28 days but I need to exclude weekends.  I found the formula to do the calculcation below but I don't really know how to use it.  HELP!

Weekday Count Formula:

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),
999)
+
(FLOOR(( EndDate__c - StartDate__c )/7)*5)

Weekend Days Count Formula:

CASE(MOD( StartDate__c - DATE(1985,6,24),7),
0 , CASE( MOD( EndDate__c - StartDate__c, 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,0,3,1,2),
3 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,0,2,1,2),
4 , CASE( MOD( EndDate__c - StartDate__c, 7),0,0,1,1,2),
5 , CASE( MOD( EndDate__c - StartDate__c, 7),0,1,2),
6 , CASE( MOD( EndDate__c - StartDate__c, 7),6,2,1),
999)
+
(FLOOR(( EndDate__c - StartDate__c )/7)*2)

Ispita_Navatar

In case if your requirment is just to calculate the number of Saturdays and Sundays between two dates then try the following:-

Subtract the one date from the other and divide by 7. That gives you whole weeks for which there is one each Saturday and Sunday. If the start date  is a Sunday or end Date is a Saturday but not both then add one to the previous result.

You may not be able to find out if a Date falls on a Sunday or saturday, but I think your atctivity will not start or end on weekend , hence it can work for you.

In case it is the other way round you can have a small invisible VF widget which does this for you in the backgroup and updates the respective field.

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

Ispita_Navatar

In case if your requirment is just to calculate the number of Saturdays and Sundays between two dates then try the following:-

Subtract the one date from the other and divide by 7. That gives you whole weeks for which there is one each Saturday and Sunday. If the start date  is a Sunday or end Date is a Saturday but not both then add one to the previous result.

You may not be able to find out if a Date falls on a Sunday or saturday, but I think your atctivity will not start or end on weekend , hence it can work for you.

In case it is the other way round you can have a small invisible VF widget which does this for you in the backgroup and updates the respective field.

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

This was selected as the best answer