You need to sign in to do that
Don't have an account?
jisaac
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)
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)
IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=3)),0.81,
IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=6)),0.86,0.88))
All Answers
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
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)
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" )
IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=3)),0.81,
IF(AND((YEAR(CloseDate) = 2009),(MONTH(CloseDate) <=6)),0.86,0.88))
**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...
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