+ Start a Discussion
SeaTecSeaTec 

What is wrong wiht this formula?

HI, I have the below formula field in the opportunity object.  It takes the closed date value from opportunity and runs the calc. It should result in either Phase1,2,3 or overdue dpeending on the date. However, as of right no i get #error# as the field value while the closed date value is 5/31/2012.

 

What am i doing wrong here?

 

THanks

IF(AND(MONTH(CloseDate) = MONTH(Today()), Year(CloseDate)=Year(Today())), "Phase3", IF(AND(CloseDate >=  DATEVALUE("2012-05-01"), CloseDate  <=  DATEVALUE("2012-06-31")) , "Phase2", IF( CloseDate-Today() > 120, "Phase1", "OVERDUE")))

TheIntegratorTheIntegrator

Not sure why, but if I just compare month and year for the second condition, there is no error anymore

 

IF(AND(MONTH(CloseDate) = MONTH(Today()), Year(CloseDate)=Year(Today())), "Phase3", IF(AND(Year(CloseDate)=2012,OR(MONTH(CloseDate)=5,MONTH(CloseDate)=6)) , "Phase2", IF( CloseDate-Today()  > 120, "Phase1", "OVERDUE")))

 

I'm also doubtful of this condition CloseDate-Today()  > 120 as this will be always 0 or negative. If the timeline goes like this: Overdue, Phase3, Phase2, Phase1, Overdue then I think you need to change to Today()-CloseDate  > 120

 

Hope that helps

SeaTecSeaTec

YOur formula thors an error when checking syntax.

 

Doesnt like this part

 

MONTH(CloseDate)=6))

TheIntegratorTheIntegrator

that's strange, I copied and pasted on a formula field again in my dev org and I get

'No syntax errors in merge fields or functions. (Compiled size: 440 characters)'

SeaTecSeaTec

June has only 30 days.  Duh.  thanks for the help though.

TheIntegratorTheIntegrator

time well spent :smileyhappy: