+ Start a Discussion
jisaacjisaac 

field update based on Close Date quarter and year

I cannot use Multi-Currency on this so am looking to update a percent field based on the Close Date Fiscal Quarter and year.

 

If the Close date is in Q1 09, the percent is 0.81

for Q2 09 it is 0.86

for Q3 09 it is 0.88 and so on.

 

I have tried using Case but I can only get that to work based on the month. How do I include the year as well?

Any suggestions?

 

Jane

 



CASE(MONTH(CloseDate ),
1, 0.81,
2, 0.81,
3, 0.81,
4, 0.86,
5, 0.86,
6, 0.86,
7, 0.88,
8, 0.88,
9, 0.88,
10, 0.88,
11, 0.88,
12, 0.88,
0.0)
Best Answer chosen by Admin (Salesforce Developers) 
SteveMo__cSteveMo__c
How about this?

IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=3)),0.81,

IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=6)),0.86,0.88))

 

Message Edited by Stevemo on 08-05-2009 05:40 PM

All Answers

JakesterJakester

I don't quite understand why you're using those specific numbers. Assuming it's just a simple business rule, and you have different simple business rules for different years, you could do something like this:

 

 

if year(CloseDate,2009 ,CASE(MONTH(CloseDate ), <4, 0.81, <7, 0.86,.88 ,if year(CloseDate,2010 ,yournextcasestatementhere

 

 

 

 

 

jisaacjisaac
It did not like the Less Than symbols - said it did not recognize them.

So now I am at this point and it is telling me that I have an extra CASE. Ideas?

IF(year(CloseDate,2009))
CASE(MONTH(CloseDate),
1, 0.81,
2, 0.81,
3, 0.81,
4, 0.86,
5, 0.86,
6, 0.86,
7, 0.88,
8, 0.88,
9, 0.88,0.88)
JakesterJakester

Oh- I thought you could use < symbols... dang. Yes, you closed out the If() too early. Try:

 

 

IF(year(CloseDate,2009) ,CASE(MONTH(CloseDate) ,1, 0.81 ,2, 0.81 ,3, 0.81 ,4, 0.86 ,5, 0.86 ,6, 0.86 , 0.88 ) ,"00" )

 

 

 

 

 

SteveMo__cSteveMo__c
How about this?

IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=3)),0.81,

IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=6)),0.86,0.88))

 

Message Edited by Stevemo on 08-05-2009 05:40 PM
This was selected as the best answer
SteveMo__cSteveMo__c

**bleep**!  you beat me to it!

 

 

Message Edited by Stevemo on 08-05-2009 05:29 PM

 

 

"**bleep**!" ?!?!?   WTF?!?!?   apparently the Board Mod has NO sense of humor... :smileyindifferent:

Message Edited by Stevemo on 08-05-2009 05:38 PM
jisaacjisaac

Jakester and Steve,

 

Thanks guys - both your formulas worked but I went with Steve's because it will be easier to maintain over the years.  I am looking down the road for formulas that will stand the test of time as I add more years and Finance changes the exchange rates we use.

 

Jane