+ 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!
gas as 6gas as 6
Thanks for sharing this script. I will surely app this on my Blockman app (https://gbhouse.info/blockman-go-mod-apk/) that hosted on WordPress.