Using a Salesforce formula to determine if a date is current or previous fiscal year

Standard

I wrote out this formula because there was a need for our customers to be able to quickly see if a date was in or out of a current fiscal year. The key component of this formula is to have a way of thinking that doesn’t think of a year as January 1 to December 31st but rather from “First day of Fiscal” to “Last day of Fiscal”.

 This formula could be used as a base if your company had a narrow criteria. The particular requirement I had was for plain text results for easier reporting.

CASE(
(if(Month(today())>9,Year(today())+1,Year(today())))-(if(
Month( <<YOURDATEHERE>> )>9,Year(<<YOURDATEHERE>>)+1,Year(<<YOURDATEHERE>>)
)),
1,”Previous FY”,
0,”Current FY”,
“Out of Scope”)

 

The nice thing with the plain text is that you get a data set that makes reporting SUPER easy because you can then group on that field and have subtotals running.

 

Enjoy the weekend!

Advertisements

4 thoughts on “Using a Salesforce formula to determine if a date is current or previous fiscal year

    • Hi There –
      Thanks for dropping by and your comment!
      Since this is a case statement, you can drop in a criteria for 2 years back like this:
      CASE(
      (if(Month(today())>9,Year(today())+1,Year(today())))-
      (if(
      Month(Sample_Date__c )>9,Year(Sample_Date__c)+1,Year(Sample_Date__c)
      )),
      2,”Previous Previous”
      ,1,”Previous FY”,
      0,”Current FY”,
      “All Else”)

      Be sure to test, test, test! Dates can be funky!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s