+ Start a Discussion
Puja khetanPuja khetan 

formula field on Activity Object

Experts ,
I am trying to execute below formula but it is thowing syntax error.
can you please help me to undertstand ,what this formula is doing also correct syntax error ?

case(mod( today()  - DATE(1985,6,24),7),
6, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0),
0, if( ActivityDate - today() >=0, 1,  if( today() -  ActivityDate >=6, 0),
1, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0),
2, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0),
3, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0),
4, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0),
5, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0))

 
Abhishek BansalAbhishek Bansal
Hi Puja,

There are two mistakes in your formula:
  1. The second if condition in all the rows have only one parameter.
  2. You are not assigning any value to the field when none of the case is matched.
I have corrected the formula syntax, please replace the values as per your requirement:
case(
mod( today()  - DATE(1985,6,24),7),
6, if( ActivityDate - today() >=-1, 1, if( today() -  ActivityDate >=9, 0,0)),
0, if( ActivityDate - today() >=0, 1,  if( today() -  ActivityDate >=6, 0,0)),
1, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0,0)),
2, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0,0)),
3, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0,0)),
4, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0,0)),
5, if( ActivityDate - today() >=-1, 1,  if( today() -  ActivityDate >=9, 0,0)),

0)
FYI: For both the cases I have added 0 in the formula.

Thanks,
Abhishek Bansal.