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 exclude weekends

hello, I am trying to create a field formula to exclude weekends but keep getting this error " Error: Syntax error. Missing ')'" 

this is my formula

TEXT ( ((DATEVALUE(NOW ()) - DATEVALUE(CreatedDate )) - (CASE(MOD(DATEVALUE(CreatedDate) - DATE (1900, 1,7),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)


I am trying to count how many business days it's been so it should just return a numeric value
Best Answer chosen by Mia Horwath
Sai PraveenSai Praveen (Salesforce Developers) 
Hi Mia,

If your return type for the formula is number please use the below formula. 
If the return type is text just add TEXT() to the below.
  
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)

If this solution helps , Please mark it as best answer.

Thanks,

All Answers

Sai PraveenSai Praveen (Salesforce Developers) 
Hi Mia,

If your return type for the formula is number please use the below formula. 
If the return type is text just add TEXT() to the below.
  
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)

If this solution helps , Please mark it as best answer.

Thanks,
This was selected as the best answer
Mia HorwathMia Horwath
worked perfectly, thanks!
Henry LopsieHenry Lopsie

Thanks, it's working perfectly. Moreover, now I am also using this code on my Payback 2 App (https://apksdownload.com/payback-2-mod/). I hosted it on a Wordpress platform.