function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mia HorwathMia Horwath 

Field Formula to calculate weekends only

Hello,
I am trying to write a formula to calculate how many weekend days have passed. I am only trying to get the weekend value so if 7 days total had passed (5 business days, 2 weekend days) I'd want my return value to be 2. This is what I have to calculate only weekdays but how do I change this to only calculate weekends? Thanks. 

CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7), 0 , CASE( MOD( DATEVALUE(NOW ()) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1), 1 , CASE( MOD( DATEVALUE(NOW ()) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1), 2 , CASE( MOD( DATEVALUE(NOW ()) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1), 3 , CASE( MOD( DATEVALUE(NOW ()) - DATEVALUE(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1), 4 , CASE( MOD( DATEVALUE(NOW ()) - DATEVALUE(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1), 5 , CASE( MOD( DATEVALUE(NOW ()) - DATEVALUE(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0), 6 , CASE( MOD( DATEVALUE(NOW ()) - DATEVALUE(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0), 999) + (FLOOR(( DATEVALUE(NOW ()) - DATEVALUE(CreatedDate) )/7)*5)
Maharajan CMaharajan C
Hi Mia,

Please try the below formula:
  
CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7),
0 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(CreatedDate), 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(CreatedDate), 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(CreatedDate), 7),0,0,1,0,2,0,3,1,2),
3 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(CreatedDate), 7),0,0,1,0,2,1,2),
4 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(CreatedDate), 7),0,0,1,1,2),
5 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(CreatedDate), 7),0,1,2),
6 , CASE( MOD( DATEVALUE(NOW()) - DATEVALUE(CreatedDate), 7),6,2,1),
999)
+
(FLOOR(( DATEVALUE(NOW ()) - DATEVALUE(CreatedDate))/7)*2)

Thanks,
Maharajan.C
CharuDuttCharuDutt
Hii Mia 
Try Below Code 
CASE(MOD( date_1__c - DATE(1985,6,24),7),
0 , CASE( MOD( date_2__c - date_1__c , 7),1,0,2,0,3,0,4,0,5,1,6,2,0),
1 , CASE( MOD( date_2__c - date_1__c , 7),0,0,1,0,2,0,3,0,4,0,5,2,2),
2 , CASE( MOD( date_2__c - date_1__c , 7),0,0,1,0,2,0,3,1,2),
3 , CASE( MOD( date_2__c - date_1__c , 7),0,0,1,0,2,1,2),
4 , CASE( MOD( date_2__c - date_1__c , 7),0,0,1,1,2),
5 , CASE( MOD( date_2__c - date_1__c , 7),0,1,2),
6 , CASE( MOD( date_2__c - date_1__c , 7),6,2,1),
999)
+
(FLOOR(( date_2__c - date_1__c )/7)*2)
Please Mark It  As Best Answer If It Helps
Thank You!