ShowAll Questionssorted byDate Posted
Mia 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 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)```

Thanks,

Sai 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)```

Thanks,
This was selected as the best answer
Mia Horwath
worked perfectly, thanks!
gas 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.