+ Start a Discussion
666Chicago666Chicago 

Need to Create Fiscal Period formula for Campaigns

Trying to create a custom field in campaigns that shows the fiscal period based off the start date of the campaign.  However, the formula I've created below isn't working. New to creating formula's so I don't know what I'm doing wrong. Keep getting following message" Error: Syntax error. Missing ','
 
 I would like the formula to look at the campaign start date and give an out put of "Q4-2006"
 
 
CASE( MONTH ( {!CloseDate} ) , 1, "Q1"YEAR(TODAY() ), 2, "Q1"YEAR(TODAY() ), 3, "Q1"YEAR(TODAY() ), 4, "Q2"YEAR(TODAY() ), 5, "Q2"YEAR(TODAY() ), 6, "Q2"YEAR(TODAY() ), 7, "Q3"YEAR(TODAY() ), 8, "Q3"YEAR(TODAY() ), 9, "Q3"YEAR(TODAY() ), "Q4")
 
Thanks in advance.
p
Scott JorgensenScott Jorgensen
This is a formula I use for the same purpose:
"FY" & CASE( MONTH( {!Date__c} ) ,
1, TEXT( YEAR( {!Date__c} ) ) ,
TEXT( YEAR( {!Date__c} ) +1 ) )

& " Q" & CASE( MONTH( {!Date__c} ) ,
1, "4",
2, "1", 3, "1", 4, "1",
5, "2", 6, "2", 7, "2",
8, "3", 9, "3",10, "3",
11,"4",12, "4",
"Problem!")
Take note that our campany's fiscal year begins in February.  The first Case() statement handles January which has the Fiscal Year match the Calendar Year whereas all other months the Fiscal Year is Calendar Year +1.

The second Case() statement simply sets the Fiscal Quarter based on which month it is.
 
CarolineRCarolineR

I know that this was posted a while ago, but I have just used the idea to build a formula for a neat summary report.  Worked really well, and I just wanted to say thanks for the tip!  Caroline