Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
SeaTec

# 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")))

TheIntegrator

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

SeaTec

YOur formula thors an error when checking syntax.

Doesnt like this part

MONTH(CloseDate)=6))

TheIntegrator

﻿﻿﻿﻿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)'

SeaTec

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

TheIntegrator

time well spent :smileyhappy: